Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Running Total not correct when adding extra field

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[datetim...
help needed
running total
Tanushree_Kapse's avatar
Tanushree_Kapse
Impactful Individual
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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..

 

    • amitchandak's avatar
      amitchandak
      Super User
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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.