Running Total not correct when adding extra field

[Anonymous]

Hi!

I have been using this running total measure for several years now and never had any problem with the result, until now.

CALCULATE(SUM(fact[Quantity]),FILTER(ALLSELECTED(calendar[datetime]),calendar[datetime]<= MAX(calendar[datetime])))

As you can see everything works fine in the screenshot below.

But when I add the field 'Ordernumber' from the same table 'fact' the running total (cumulative) is not working anymore. I really do not understand why? I hope some of you do;) please enlighten me!

[Anonymous]

Hi @amitchandak 

This first measure with ALL does work, but I want to be able to filter on a Ordernumber and then it does not show the correct amount. The second measure with ALLSELECTED is almost correct!

The only thing is that the amount in yellow are correct, but only for the last row, very strange. 

[Tanushree_Kapse]

@Anonymous ,
 Try this measure:

RunningTotal =
CALCULATE (
 SUM ( fact[Quantity] ),
 FILTER ( ALL ( fact ), calendar[Datetime] <= MAX ( calendar[Datetime] ) );
 VALUES ( fact[ordernumber )
)

I hope this helps!

Mark this as a solution if it answers your queation. Kudos are always appreciated.

Thanks! 

[Anonymous]

Hi @Tanushree_Kapse 

The measure not working, because the ALL includes the Fact and therefore I cannot filter the calendar. And could you maybe explain why I need tho add the Values part. I never had to use it before to get the correct outcome.

RunningTotal =
CALCULATE (
 SUM ( fact[Quantity] ),
 FILTER ( ALL ( fact ), calendar[Datetime] <= MAX ( calendar[Datetime] ) );
 VALUES ( fact[ordernumber )
)

[Tanushree_Kapse]

Hi @Anonymous ,

Sorry for the typo.

Try this:

RunningTotal =
CALCULATE (
 SUM ( fact[Quantity] ),
 FILTER ( ALL ( calendar), calendar[Datetime] <= MAX ( calendar[Datetime] ) );
 VALUES ( fact[ordernumber )
)

Also here, the VALUES() puts the Ordernumber into the filter context of the CALCULATE()-statement. Otherwise you would have running totals on the dates, but it would be the same for all your Ordernumbers. 

Thanks

[Anonymous]

Hi @Tanushree_Kapse 

That is not working unfortunatly..

Even without ordernumber it is not working

When I remove the values part it does, but not with the ordernumber..

[Tanushree_Kapse]

@amitchandak Can you please help on this?

[amitchandak]

@Tanushree_Kapse , @Anonymous , Based on what I got so far. So if take order number from fact and running total logic from the calendar then it running total within the order number. Value for *19 and *20 repeated for null value rows .

Try

RunningTotal =
CALCULATE (
SUM ( fact[Quantity] ),
FILTER ( ALL ( fact), fact[Datetime] <= MAX ( fact[Datetime] ) );
)

or

RunningTotal =
CALCULATE (
SUM ( fact[Quantity] ),
FILTER ( ALLSELECTED ( fact), fact[Datetime] <= MAX ( fact[Datetime] ) );
)

[Anonymous]

Hi @amitchandak 

The second measure is almost correct, but as you can see the cumulative is not working correctly in some cases. See the yellow amounts where only the last of the three is correct.

[Anonymous]

@amitchandak @Tanushree_Kapse Any update on this problem?