Automatic and rolling comparison between the last 12 month and the last 12 months from last year

[Wedding55]

Dear All,

i`m searching for a solution to calculate several dimension of our sales figures.

In Table “Sales” I have the turnover for each productgroup and country from the last two financial years sorted by month (I don’t need a daily comparison). Starting on 01.07.2017 till 30.06.2019. Now I want to fulfill the table with the sales figures from the last months. Now till 30.09.2019. Next month till 31.10.2019 and so on.

The dimension are as followed:

12: Turnover from the last 12 months (01.10.2018-30.09.2019) compared to

the Turnover from the last 12 month last year (01.10.2017-30.09.2018)

6: Turnover from the last 6 months (01.04.2019-30.09.2019) compared to

the Turnover from the last 6 month last year (01.04.2018-30.09.2018)

3: Turnover from the last 3 months last year (01.07.2019-30.09.2019) compared to

the Turnover from the last 3 month (01.07.2018-30.09.2018)

1: Turnover from last months (01.09.2019-30.09.2019) compared to

the Turnover from the month last year (01.09.2018-30.09.2018)

I would like to have a table in Power bi that calculate from the last actual date. In the excel chart the basis is 30.09.2019. Next month I load the data from 31.10.2019 in Power Bi and the basis of the calculation should be 31.10.2019. In the attached Excel File (20191011 calcuation logic) you can see the logic. I hope you understand it and its clear enough.?

Attached you see my Power Bi tables and the Excel Upload file (20191010 Test).

Excel Upload File:

https://www.file-upload.net/download-13746583/20191010Test.xlsx.html

Excel calculation logic:

https://www.file-upload.net/download-13746581/20191011calcuationlogic.xlsx.html

PowerBi:

https://www.file-upload.net/download-13746582/20191011Test.pbix.html

If you have any questions please ask.

I would be very grateful

Best Regards

[amitchandak · Accepted solution]

You should able to get all rolling calc in this manner. Change and create different measure as required

last 12 Months = ( 
var _Cuur_start = MINX('Compare Date',DATEADD('Compare Date'[Compare Date],-12,MONTH))
VAR _Curr_END = Max('Compare Date'[Compare Date])
var _Last_start = MINX('Compare Date',DATEADD('Compare Date'[Compare Date],-24,MONTH))
VAR _Last_END_END = MAXX('Compare Date',DATEADD(ENDOFMONTH('Compare Date'[Compare Date]),-12,MONTH))

var _last12 = CALCULATE(SUM(Sales[Sales Amount]),Sales[Sales Date]>= _Cuur_start && Sales[Sales Date] <=  _Curr_END )
var _last12_before12 = CALCULATE(SUM(Sales[Sales Amount]),Sales[Sales Date]>= _Last_start && Sales[Sales Date] <=  _Last_END_END )
return 
_last12 -_last12_before12 )

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

[Wedding55]

Dear Amitchandak,

thank you for your answer @amitchandak . Is this one big meausre? Or how i have to handle it?

When i try the first block, i get a Syntax Error.

I`m still ja Beginner with Power BI 🙂 So many things in Power BI are new for me.

Thanks in advance.

Best Regards

[amitchandak]

Share the formula you created

[Wedding55]

Dear amitchandak,

it seems to work great. Yesterday it was my fault.

Now i set a column with the last 12 month with this meausre:

Test last 12 Months = ( 
var _Cuur_start = MINX(Sales;DATEADD(Sales[Datum];-12;MONTH))
VAR _Curr_END = Max(Sales[Datum])
var _Last_start = MINX(Sales;DATEADD(Sales[Datum];-24;MONTH))
VAR _Last_END_END = MAXX(Sales;DATEADD(ENDOFMONTH(Sales[Datum]);-12;MONTH))

var _last12 = CALCULATE(SUM(Sales[Turnover]);Sales[Datum]>= _Cuur_start && Sales[Datum] <=  _Curr_END )
var _last12_before12 = CALCULATE(SUM(Sales[Turnover]);Sales[Datum]>= _Last_start && Sales[Datum] <=  _Last_END_END )
return 
_last12 -_last12_before12 )

And another columns who shows me the last 12 months befor the last 12 month from the measure above.

Test Test last 24 Months = ( 
var _Cuur_start = MINX(Sales;DATEADD(Sales[Datum];-24;MONTH))
VAR _Curr_END = Max(Sales[Datum])
var _Last_start = MINX(Sales;DATEADD(Sales[Datum];-36;MONTH))
VAR _Last_END_END = MAXX(Sales;DATEADD(ENDOFMONTH(Sales[Datum]);-24;MONTH))

var _last24 = CALCULATE(SUM(Sales[Turnover]);Sales[Datum]>= _Cuur_start && Sales[Datum] <=  _Curr_END )
var _last24_before24 = CALCULATE(SUM(Sales[Turnover]);Sales[Datum]>= _Last_start && Sales[Datum] <=  _Last_END_END )
return 
_last24 -_last24_before24 )
-
( 
var _Cuur_start = MINX(Sales;DATEADD(Sales[Datum];-12;MONTH))
VAR _Curr_END = Max(Sales[Datum])
var _Last_start = MINX(Sales;DATEADD(Sales[Datum];-24;MONTH))
VAR _Last_END_END = MAXX(Sales;DATEADD(ENDOFMONTH(Sales[Datum]);-12;MONTH))

var _last12 = CALCULATE(SUM(Sales[Turnover]);Sales[Datum]>= _Cuur_start && Sales[Datum] <=  _Curr_END )
var _last12_before12 = CALCULATE(SUM(Sales[Turnover]);Sales[Datum]>= _Last_start && Sales[Datum] <=  _Last_END_END )
return 
_last12 -_last12_before12 )

I dont know if its the easiest way but now i can compare the two columns. Thats all what i need. Thank you @amitchandak 🙂

Best Regards