DAX Measure help

[Tomasjhussey]

 Hi,

I've been expericing an unexpected behaviour with a cumulative dax measure

below the standard measure that I am using and a screenshot that illustrates my problem

Cumulative Loss =

CALCULATE (

SUM ( Forum[Loss] ),

FILTER ( ALL ( Forum[Year] ), Forum[Year] <= MAX ( Forum[Year] ) )

)

 

All graphs illustrate the same Measure described above and the matrix has the Loss for each year (not cumulative)

The issue is in the bottom left graph (affected by the Property Type Slicer) where the cumulative loss drops in year 9, apparently due to an absence of loss in SA & Apartments as the matrix shows.

I was expecting a line equal to the one in the top left chart where it acumulates both SA and ACT numbers.

Also the graph on the top right shows the expected Aparment cumulative loss, so I dont fully understand why the bottom left one is showing something different.

Any suggestions?

[v-alq-msft]

Hi, @Tomasjhussey 

Based on your description, I created data to reproduce your scenario. However, It works correctly. I assume that maybe other tables in the data model cause the problem. 

Here is my result:

You may try to create a calculated table as follows.

Year table = Distinct(Forum[Year])

Then you can use the 'Year' column in Year table to create the line chart to check whether it appears normal. Please tell me your testing result. Thanks.

Best Regards

Allan

[danextian]

Hi @Tomasjhussey ,

Looking at the 3rd chart, there's about 9.9K gain causing a drop. Have you checked on the visual filter? It's probably filtered by something which affects just year 9. Otherwise, could you post your data or model?

[Tomasjhussey]

Hi @danextian 

Thanks for your interest, the complete data is shown in the matrix. I can't publish the PBIX. 

And yes, there is a slicer affecting that visual, in the screenshot you can see that there is a slicer is affecting 2 graphs, also a report level filter for SA and ACT . Other than that there is no visual level filter. 

Thanks