Running Total from a given Measure

[Anonymous]

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

[amitchandak]

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

[jdbuchanan71]

@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]

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 · Accepted solution]

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