cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Wedding55
Frequent Visitor

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Automatic and rolling comparison between the last 12 month and the last 12 months from last yea

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.





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


View solution in original post

4 REPLIES 4
Super User IV
Super User IV

Re: Automatic and rolling comparison between the last 12 month and the last 12 months from last yea

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.





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


View solution in original post

Wedding55
Frequent Visitor

Re: Automatic and rolling comparison between the last 12 month and the last 12 months from last yea

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

Super User IV
Super User IV

Re: Automatic and rolling comparison between the last 12 month and the last 12 months from last yea

Share the formula you created





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


Wedding55
Frequent Visitor

Re: Automatic and rolling comparison between the last 12 month and the last 12 months from last yea

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

 
 
 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors