Cumulative calculation stopped working when using data model in the service

[99_pct]

I have a report (let's call it 'User Report') which uses a dataset from a PowerBI semantic dataset (let's call that one 'Source Report') already in the workspace/service. 

In the Source Report I have created measures to calculate cumulative values (example below). As of this Monday (Sept 30th), in the User Report, they no longer calculate the cumulative values, only show the individual values. This is true whether I view that report in the browser via the service, or by opening the PBIX file in Dekstop. It is also true if I look at the Source Report in the browser/service. However, when I view the same numbers in the Source Report opened in Desktop, they still show the cumulative values correctly.

Cumulative Budget = CALCULATE (

    SUM('Actuals vs Budget'[Budget]),

    KEEPFILTERS ('Month list'[Month] <= MAX ('Month list'[Month]) )

    )

FYI, the [Month] field is not a date field and is in the format of M99 (e.g. M01, M02, etc)

Has anyone else seen this behaviour? It was working fine last week.

(Not sure if my DAX syntax was always a bit 'iffy' and maybe the PowerBI has just tightened up on iffy code!)

Any thoughts welcomed (or suggestions for alternative code that I can try to see if I can get around this behaviour).

Thanks

[Anonymous · Accepted solution]

Thanks for GilbertQ's concern about this issue.

Hi, @99_pct 

First, you can test if it works by changing the DAX to what GilbertQ  recommends, or the DAX I provide below below:

Cumulative Budget = 
SUMX(
    FILTER(
        'Month list',
        'Month list'[Month] <= EARLIER('Month list'[Month])
    ),
    'Actuals vs Budget'[Budget]
)

Secondly you need to make sure that the [Month] field is formatted and of the correct type. Because it is not a date field, Power BI may handle it differently on the server side than on the desktop. You can try converting the [Month] field to a date type or make sure it is consistent across all reports.

Or try deleting and recreating the metric in the Source Report and User Report, which can sometimes reset the potential problem.

Sometimes caching issues can also cause inconsistent data, try clearing your browser cache.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GilbertQ]

Hi @99_pct 

I dont think the KEEPFILTERS is the right way to do this.

Could you try the following below

Cumulative Sales = 
CALCULATE(
    SUM('Actuals vs Budget'[Budget]),
    FILTER(
        ALLSELECTED('Month list'[Month]),
        'Month list'[Month] <= MAX('Month list'[Month])
    )
)

I would also highly recommend using date values to make it easier for it to work.