Hi everyone
I am struggling with how to calculate the parallel period sales for my custom campaign activities which varies between 2-7 days.
This is the current DAX im using:
Sales PP = //previous period
VAR Products = VALUES( 'factSales'[ARTICLEID] )
VAR StartDate = MIN ( 'factSales'[CampaignStartDate] )
VAR EndDate = MAX ( 'factSales'[CampaignEndDate] )
VAR Length = DATEDIFF( StartDate, EndDate, DAY ) + 1
VAR StartDateParallelPeriod = StartDate - Length
VAR EndDateParallelPeriod = StartDate - 1
RETURN
CALCULATE (
[Sales],
FILTER (
ALL ( 'factSales' ),
'factSales'[ArticleID] IN Products &&
'factSales'[OrderDate] >= StartDateParallelPeriod &&
'factSales'[OrderDate] <= EndDateParallelPeriod
)
)
Lets say a campaign start on a Monday and ends on a Wednesday which is three days, then I would like to calculate the sales of the products for the previous week Monday to Wednesday. But the current logic just shifts the days three days backwards which is Monday → Saturday and Wednesday → Monday.
Any idea on how to perform the correct parallel period calculation?
This is my fact table which includes all the normal thing but I have also added the campaign start and end date for each campaign ID.
You should be able to use DATEADD(StartDate,-7,day) and DATEADD(EndDate,-7,day) to subtract 7 days from the specified MIN and MAX campaign date variables. This will go backward exactly one week for each date, and actually should handle campaigns that last longer than a week.
This would be instead of StartDate - Length and StartDate - 1, which are what are giving you the current results. You also would not need the Length variable anymore.
Based on your requirement to look at the same days in the previous week, you actually do not care how long the campaign lasted.
The -7 supports your requirement of looking at the same period 1 week prior. By subtracting 7 days from the StartDate and 7 days from the EndDate, you shift the previous period’s dates backward by 7 days, or 1 week.
@jpratt okey now im following
How would such a DAX look like?
I tried with this one but did not work, just showed blank for all rows and I could not reuse the “VAR StartDate” because it was not recognized by the DATEADD
Sales PP v2 = //previous period
VAR Products = VALUES( 'factSales'[ARTICLEID] )
VAR StartDateParallelPeriod = DATEADD( 'factSales'[CampaignStartDate], -7 , DAY )
VAR EndDateParallelPeriod = DATEADD( 'factSales'[CampaignEndDate], -7 , DAY )
RETURN
CALCULATE (
[Sales],
FILTER (
ALL ( 'factSales' ),
'factSales'[ArticleID] IN Products &&
'factSales'[OrderDate] >= StartDateParallelPeriod &&
'factSales'[OrderDate] <= EndDateParallelPeriod
)
)
Per the solution in this post from one of Enterprise DNA’s own gurus, you just need to wrap the variable name with curly braces. So it would be “{StartDate}” and “{EndDate}”. For the original post’s version, it should look like this:
VAR Products = VALUES( ‘factSales’[ARTICLEID] )
VAR StartDate = MIN ( ‘factSales’[CampaignStartDate] )
VAR EndDate = MAX ( ‘factSales’[CampaignEndDate] )
VAR StartDateParallelPeriod = DATEADD( {StartDate}, -7 , DAY )
VAR EndDateParallelPeriod = DATEADD( {EndDate}, -7 , DAY )
You should be able to simplify the variables even more as in your last post, but because you used MIN and MAX functions in the original post you would need to include them in the more-simplified version:
VAR Products = VALUES( ‘factSales’[ARTICLEID] )
VAR StartDateParallelPeriod = DATEADD( MIN(‘factSales’[CampaignStartDate]), -7 , DAY )
VAR EndDateParallelPeriod = DATEADD( MAX(‘factSales’[CampaignEndDate]), -7 , DAY )
So, I downloaded the PBIX and took a look. Two major things I noted first off: You have all of your measures in the tables; and the Campaign Start/End Dates in the dimCampaign table are text values which are all 1 week apart and do not match the dates in the factSales table.
Ideally you would have the actual campaign start/end dates in the dimension table and they would be dates. I stuck with what you have on the factSales table for now.
I also moved the measures to their own table, per Sam’s training. Makes it easier to keep things straight.
Now the big issue, before presenting a further previous period optimization: You are looking for total sales from the prior period based on OrderDate, but there are no orders in the factSales data meeting any of the criteria. The latest CampaignEndDate is 7/23/2021, so the most recent OrderDate that could return data from a wekk prior is 7/16/2021. BUT, the first OrderDate in the Sales table is 7/17/2021. So even if the previous period formula is working, there is no data to return so technically a blank value is correct
While monkeying with this, I came up with the following possible solution that is even further optimized than what we were discussing previously. I say possible because there is no data to test it on. Once there is data to test on, the question of what works and doesn’t work will depend on the context actually used for the CALCULATE() function. But Power BI did accept the DAX without choking .
Sales PP Measure = //previous period
VAR Products = VALUES( factSales[ARTICLEID] )
RETURN
CALCULATE (
[Sales],
FILTER (
ALL ( factSales ),
factSales[ArticleID] IN Products &&
factSales[OrderDate] >= DATEADD( factSales[CampaignStartDate], -7 , DAY ) &&
factSales[OrderDate] <= DATEADD( factSales[CampaignEndDate], -7 , DAY )
)
)
.