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

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's avatar
Tanushree_Kapse
Icon for Impactful Individual rankImpactful 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
        Icon for Super User rankSuper 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] ) );
        )