Forum Discussion
PowerBi cumulative calculation goes wrong
Hi all,
I'm in a loss in this matter, I've tried many DAX formulas and read many forums but unfortunately I didn't found the solution.
This is the issue
I've created a calculated Line Chart but it's showing the wrong cumulative turnover. Currently I'm using the formula that I found on this forum:
Cumulative 2 =
CALCULATE (
SUM ( 'Invoice lines'[Sales] ),
FILTER (
ALLSELECTED(BI-Dates),
'BI-Dates'[Date] <= MAX ( 'BI-Dates'[Date] )
)
)But the issue is that it's not showing data when there is no turnover is the current month.
So let's say that I have two product groups - Cars and Motors. When I select them both and also select month 1 till 9 it says that I have a turnover of 9000 in month 8. But when I select month 1 till 8 it says that I have a turnover of 8000 in month 8 - that's because I didn't sold a Car in month.
Because of the fact there isn't sold anything PowerBI doens't registers a 0 but just nothing... if he would registers a 0 the problem was probably solved....
Does someone know how I could solve this issue?
Hi TJacobs,
Based on my test, the formula below should work in your scenario. :smileyhappy:
Sales Cumulative = CALCULATE ( SUM ( 'Blad1 (2)'[Turnover total]), FILTER ( ALLSELECTED('Blad1 (2)'), 'Blad1 (2)'[Date_Month] <= MAX('Blad1 (2)'[Date_Month]) ) )Regards
5 Replies
Can you post some sample data?
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Hi TJacobs,
Based on my test, the formula provided above should work in your scenario. Could you share a sample pbix file which can reproduce the issue, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. :smileyhappy:
Regards
Hello,
Thanks for you replies!
Here is the link to the example PBIX and Excel file:
To reproduce the issue: please select all the product groups except subgroup ''Damage'' and move the cursor to the second month in the line chart. The line chart now says that there is a turnover of ''5000''. Now when you deselect the third month it says that you have a turnover of ''3500'' but it should be ''5000'' like the green card is displaying.
Hi TJacobs,
Based on my test, the formula below should work in your scenario. :smileyhappy:
Sales Cumulative = CALCULATE ( SUM ( 'Blad1 (2)'[Turnover total]), FILTER ( ALLSELECTED('Blad1 (2)'), 'Blad1 (2)'[Date_Month] <= MAX('Blad1 (2)'[Date_Month]) ) )Regards
Yep! now it's working!
Thanks! :)
