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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

Please try my custom visuals

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

Please try my custom visuals

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

Please try my custom visuals

@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

Please try my custom visuals

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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