Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION

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

jdbuchanan71_0-1594662588493.png

 

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

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

jdbuchanan71
Super User
Super User

@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
Not applicable

Hi,

 

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

 

sarthaks_0-1594661795321.png

 

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

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

jdbuchanan71_0-1594662588493.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.