Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Running Total from a given Measure

Need help in calculating Running/Cummulative total

 

I have a given measure that basically counts units grouped by date

 

Measure1:=CALCULATE(DISTINCTCOUNT(Inv_Fact_Inventory[CURINV_CARGO_ID])
                                    ,Inv_Fact_Inventory[Dashboard]="Current Inventory"
                                    ,Inv_Fact_Inventory[Inventory Status]="ASSIGNED"
                                    ,Inv_Fact_Inventory[Inventory Order Type]="TENDERED NOT SHIPPED")+ [ShippedCount]

Now from this measure i need to calculate running total, so i tried to use the below measure , but its not returning the correct result

Measure2 := SUMX( FILTER ( Inv_Fact_Inventory, 
						Inv_Fact_Inventory[Dashboard]="Current Inventory" && Inv_Fact_Inventory[Inventory Status]="ASSIGNED" && Inv_Fact_Inventory[Inventory Order Type]="TENDERED NOT SHIPPED" 
						&& Inv_Fact_Inventory[Received Date] <= MAX(Inv_Fact_Inventory[Received Date])),[Measure1])

 

When i checked the database Measure2 is not giving the correct value. Please help here

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    You need the date on the x axis for the running total to be calculated over.

     

4 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    6 years ago

    Anonymous 

    Try it like this.

     

    Measure 2 :=
VAR _MaxDate =
    LASTDATE ( Inv_Fact_Inventory[Received Date] )
RETURN
    CALCULATE (
        [Measure1],
        FILTER (
            ALLSELECTED ( Inv_Fact_Inventory[Received Date] ),
            Inv_Fact_Inventory[Received Date] <= _MaxDate
        )
    )

    If your model has a calendar table and you are displaying the trending using values from that table you would need to modify the measure to look at the calendar table instead.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

       

      I'm not seeing any difference between both the measures. Both are returning the same value as seen below.

       

       

      Here are the measure definition:

       

      Measure5 = CALCULATE(DISTINCTCOUNT(Inv_Fact_Inventory[CURINV_CARGO_ID])
                                    ,Inv_Fact_Inventory[Dashboard]="Current Inventory"
                                    ,Inv_Fact_Inventory[Inventory Status]="ASSIGNED"
                                    ,Inv_Fact_Inventory[Inventory Order Type]="TENDERED NOT SHIPPED")



Measure6 = VAR _MaxDate =
    LASTDATE ( Inv_Fact_Inventory[Received Date] )
RETURN
    CALCULATE (
        [Measure5],
        FILTER (
            ALLNOBLANKROW( Inv_Fact_Inventory[Received Date] ),
            Inv_Fact_Inventory[Received Date] <= _MaxDate
        )
    )

       

       

      Am i missing something. Pleas advice

      • jdbuchanan71's avatar
        jdbuchanan71
        Super User
        6 years ago

        You need the date on the x axis for the running total to be calculated over.

         

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , In Quick measure you have an option for running total, try that if that can work for you