Forum Discussion
DAX
I´m trying to analyze the impact of marketing campaings. To do so I use a DAX measure to calculate the total sales over a period of time. That period normally goes beyond natural years (i.e. 2016-10 to 2017-02). I have the year-month as integer as row context. The measure I´m using to get the cumulated sales for the selected period is this (and it´s working fine).
CampaignSalesCummulative=CALCULATE(
SUM([CampaignSales]);
FILTER(
ALLSELECTED('Calendar'[Year-Month Integer]);
ISONORAFTER('Calendar'[Year-Month Integer]; MAX('Calendar'[Year-Month Integer]); DESC)
)
)
the issue arieses when´I´m trying to retrieve in the same row the cumulated sales for year t-1. With the same period mentioned above it would be 2015-10 to 2016-02. I can get the data for the period 1 year back with the below dax formula but I´m not able to make it cumulative.
CampaignSalesCummulative_LY=CALCULATE([CampaignSalesCummulative2];DATEADD('Calendar'[Date];-1;YEAR))
2 Replies
Hi david7f,
To return the cumulative total for last year same period, you can create a measure below:
CumulativeTotalLY = CALCULATE(SUM(Sheet1[Amount]),FILTER(ALL('Sheet1'),YEAR('Sheet1'[Date])=YEAR(MAX('Calendar'[Date]))-1 && MONTH('Sheet1'[Date])<=MONTH(MAX('Calendar'[Date]))))
You can see attached pbix file.
Best Regards,
Qiuyun YuFirst of all thanks for your time to provide a solution. Your formula works fine but the issue is that marketing campaigns normally go beyond calendar year. I´ve completed in your pbix file my real problem. As can be seen I can get the cumulated amount for the selected period (i.e. 20177 to 20182), that works fine. But if i try to show in the same line the cumulated amount for the previous year it just doesn´t work, te row filter context breaks it.
https://www.dropbox.com/s/id3xtmrppid94f5/CumulativeTotalLY.pbix?dl=0
