PowerBi cumulative calculation goes wrong

[TJacobs]

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?

[TJacobs]

Hello,

Thanks for you replies! 

Here is the link to the example PBIX and Excel file:

https://siennonline-my.sharepoint.com/personal/teun_jacobs_sienn_com1/_layouts/15/guestaccess.aspx?f...

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. 

[v-ljerr-msft · Accepted solution]

Hi @TJacobs,

Based on my test, the formula below should work in your scenario. 

Sales Cumulative = 
CALCULATE (
    SUM ( 'Blad1 (2)'[Turnover total]),
    FILTER (
        ALLSELECTED('Blad1 (2)'),
        'Blad1 (2)'[Date_Month] <= MAX('Blad1 (2)'[Date_Month]) 
    )
)

Regards

[TJacobs]

Yep! now it's working! 

Thanks! 🙂

[v-ljerr-msft]

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. 

Regards

[Greg_Deckler]

Can you post some sample data?

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490