Calculate sales before and after particular event only for customers participated in this event

DAX DAX Calculations
3

@Arturs ,

See how this works for you:

image
image1004×456 34.7 KB

In order to get this to work, before addressing the DAX measures I needed to fix a few things with data model:

  1. Calendar table was not marked as a date table, which is important for time intelligence analyses like this one
  2. Your two date fields in the Campaign table were not set as type date, but instead type text, again which would not allow for the necessary time intelligence calculations. I reset these two to type date.
  3. I’m not sure what your slicer is supposed to be doing – it is using a field from a hidden disconnected table, so given that it is not part of the data model, nor incorporated into the DAX it is not doing anything currently and I wasn’t sure what intent was, so I left it unchanged.

Once the above changes were made, the DAX measures were pretty straightforward – just using CALCULATE to change the context on the Total Sales measure to: before the initiation date of the selected campaign, during the campaign itself, and after the close date of the campaign.

Here’s the “before” measure for example:

Sales BEFORE campaign = 
CALCULATE(
    [Total Sales],
    FILTER(
        Orders,
        Orders[order_date] <  RELATED( 'Coupon campaigns'[created_at] )
    )
)

If you haven’t already, I would strongly recommend going through the Ultimate Beginner’s Guide to DAX Course. @sam.mckay does an outstanding job explaining the concepts used to create the measures in this solution.

I hope this is helpful. Full solution file attached below.

– Brian
EDNA Forum - Coupon campaigns report - Solution.pbix (2.6 MB)

1 Like