I’m pretty new to DAX, so struggling with some advanced calculations.
I have this simple model with two tables connected by campaign ID 1:* :
Campaigns
Orders
what I want to achieve is to display how much sales were done by customers who did use the campaign before and after the particular campaign. I imagine it to look something like this (id is Coupon campaign Id). Sales is a simple measure summing up Total sales column from Orders table :
In order to get this to work, before addressing the DAX measures I needed to fix a few things with data model:
Calendar table was not marked as a date table, which is important for time intelligence analyses like this one
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.
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.
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.
This always confuses me. Whenever I mark calendar tables as Date tables it’s breaking Hierarchy. Also, at least some of intelligence formulas seams to work even without calendar table being marked as Date table
Not sure why this happened in copy, but they are as Dates in the original report.
Sorry, I probably did not explain well enough my problem. It’s not about sales associated with the particular campaign, but the sales associated with the customers who also did sales within a particular campaign.
So, in general the logic for calculation should be something like this: whenever particular campaign is selected, the list of customers who participated in this campaign is collected, then sales only for these customers is calculated for the period BEFORE/AFTER the campaign. this is also a place where slicer (3.point) is coming into a play. It sets how many days offset from campaign’s START/END sales should be calculated.
The main goal of all this trouble is to be able to evaluate did particular campaign increased the sales of those customers who participated in it.
If something is not clear, please, feel free to ask. And thank you very much for your time!
To better understand what “Mark as date table” actually does, I highly recommend reviewing the article/video below.
Thanks very much for the clarification as to the analysis you want. That’s an interesting question, and I know how I will structure the DAX to do it. However, before proceeding with a solution, I wondered if you had a version of the data model with the Customer dimension built out? That will be helpful for the revised analysis.
OK, even without a proper Customer table, I was able to construct this analysis from the Environment_ID field. Here’s the general approach used, with example here of the Total Sales for Participating Customers After the Campaign:
Tot Sales Partic Cust X Days AFTER Camp =
VAR SelCampaign =
SELECTEDVALUE( 'Coupon campaigns'[id] )
VAR vTable =
FILTER( VALUES( 'Orders'[environment_id] ), [Sales DURING campaign] > 0 )
VAR TotSalesPartCustAfter =
CALCULATE(
[Total Sales],
FILTER(
ALL( 'Orders' ),
'Orders'[environment_id]
IN vTable
&& 'Orders'[order_date] > SELECTEDVALUE( 'Coupon campaigns'[invalidated_at] )
&& 'Orders'[order_date]
<= SELECTEDVALUE( 'Coupon campaigns'[invalidated_at] ) + [Harvest Day Range]
&& 'Orders'[Coupon campaign id] = SelCampaign
)
)
RETURN TotSalesPartCustAfter
The general approach was to filter only the customers participating in the selected campaign (in vTable) and then calculate Total Sales for those customers only from the day after the end of the campaign through that date plus the selected # of days from the slicer.
@BrianJ
Thanks for sharing. I did read the article and now better understand why my DAXs were working without marking the date table and also see now why it’s a better to explicitly mark Calendar/Date table as date table
Sorry for delayed answer. Wow, your solution seams to work . I was able to follow the logic and did small correction. I believe this line limits orders to the particular campaign, while there will never be any order Before or After campaign with the ID of this campaign, as it is not started yet, or is already ended. Anyway, after removing this line, I got the Sales from these measures
There is one confusing moment for me left. When I make a simple table with dates and a single campaign is selected, and all 3 measures added - Before, During, After, I expect to see result like this (sales are random, just to illustrate expectations):
. I was able to follow the logic and did small correction. I believe this line limits orders to the particular campaign, while there will never be any order Before or After campaign with the ID of this campaign, as it is not started yet, or is already ended. Anyway, after removing this line, I got the Sales from these measures 
