Cumulative dax doesnt return correct values.

[Anonymous]

Hi Guys,

I really need your help. Below you see mine DAX code which will give me Anti cumulative data. Unfortunatelly when I use this DAX then as you can see I dont get the correct result. I tried this DAX code in another file with simple test data and this worked perfectly.

This means that mine code works. But the question is why does this not work in mij data model?

Below you see a couple of information about mine data model en DAX code.

This is the 2 tables which I am talking about. There is not a date table. the date columns are in dim trade table. dim_trade_surrkey is the PK and FK.

Thanks in advance!

[Anonymous]

Hi guys,

I think I am almost there. Obviously I need to use Allselected instead of ALL then I am getting the right result. There is only one thing that I really dont understand.

Below you see 2 tables in both tables I have Year and trade_number as hierarchy. In table 1 I have a filter which is saying face value must be greater then 0. As you can see then I dont get the running total/cumulative. And in table 2 there is not a filter for face value. and the running total works correctly. 


But when I remove trade_number and the filter of face value is greater then 0 is active then the running total works too. So why is that I really dont understand?!


Thanks in advance!

[Ashish_Mathur]

Hi,

There should be a Date column in the Fact Table as well.

[amitchandak]

@Anonymous , Try like example

calculate(Sum(Table[Value]), filter(all(Table), Table[Year] <= Max(Table[Year]) ) )

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.