Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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
Share the formula you created
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |