cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NI
New Member

Running Total by date based on value in another column

I want a running total by date for the Amount Column which should be different for each Security ID.Capture.PNG

 

2 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion

Hi @NI

 

First add an Index Column from Query Editor or Power Query

 

6500.png

Regards,
Zubair


View solution in original post

@NI

 

Now you can get the expected Cumulative  Values using this Calculated Column

 

Cumulative =
CALCULATE (
    SUM ( TableName[Amount] ),
    FILTER (
        ALLEXCEPT ( TableName, TableName[Security ID] ),
        TableName[Index] <= EARLIER ( TableName[Index] )
    )
)
Regards,
Zubair


View solution in original post

4 REPLIES 4
Zubair_Muhammad
Community Champion
Community Champion

Hi @NI

 

First add an Index Column from Query Editor or Power Query

 

6500.png

Regards,
Zubair


View solution in original post

@NI

 

Now you can get the expected Cumulative  Values using this Calculated Column

 

Cumulative =
CALCULATE (
    SUM ( TableName[Amount] ),
    FILTER (
        ALLEXCEPT ( TableName, TableName[Security ID] ),
        TableName[Index] <= EARLIER ( TableName[Index] )
    )
)
Regards,
Zubair


View solution in original post

hi @Zubair_Muhammad 

I have a follow up qustion about your amazing explanation, 

let's assume I have another "Country" column at the table, which I want to take as a paramater while calculating the running total, so that the cumulative total column will show not only cumulative total over time by securtiy Id but also by country, like in the example below- how should I revise the calculation in order to do so? 

 

Thanks ! 

 

Test.PNG

@NI

 

See the attached file here

 

6501.png

Regards,
Zubair


Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.