DAX

[david7f]

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))

[v-qiuyu-msft]

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]

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