Forum Discussion

david7f's avatar
david7f
Frequent Visitor
8 years ago

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 ...
Data Modeling
v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
8 years ago

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 Yu 

david7f's avatar
david7f
Frequent Visitor
8 years ago

First 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