mardi 25 mai 2021

DAX running total calendar date issue with IF statement

I have a simple if statement for calculating values on date

IF statement = 
IF(AND(MAX('Calendar'[Date]) > [Last date], SUM('Calendar'[Value1]) > COUNT('Value2'[Value2])), [diff_e_2],
 IF(AND(MAX('Calendar'[Date]) > [Last date], SUM('Calendar'[Value1]) < COUNT('Value2'[Value2])), [diff_t_2],
  IF(MAX('Calendar'[Date]) < [Last date], [diff_t_2],
     IF(MAX('Calendar'[Date]) = [Last date], [diff_e_2](), 
[diff_e_2])
)))

[Last date] = TODAY() + 10

This is working fine and I can get the graph I want. But when I create a running/cumulative total it doesn't work on the date [Last date].

Running total = 
CALCULATE(([IF statement]), FILTER(ALLSELECTED('Calendar'), 'Calendar'[Date]<=MAX('Calendar'[Date])))

Otherwise it works but in this case [Last date] should add 0 but instead it is deducting 2. And most times it is deducting values and can't find the reason.

Any idea how to fix this?

Thanks.

Br, Joona

Aucun commentaire:

Enregistrer un commentaire