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

Cumulative Sum/Running Total help

Hi,

 

Without success, i have been trying to create a cumulative sum / running total for the past few days. I have read quite a few of the existing threads already, unfortunately this didn't resolve the issue. 

 

I have a Transaction data set with credit and debit amount for each date and from each 'Source'.  However, not every date contain transactions, leading to 'null' values in the data. The PBIX file can be found here: PBIX file

 

My measures are the following; the running total is the 'standard version' i find everywhere:

BalanceChange = SUM(Transactions[Credit Amount]) - SUM(Transactions[Debit Amount])

RunningTotal = 
CALCULATE (
   SUM(Transactions[Credit Amount]) - SUM(Transactions[Debit Amount]);
    FILTER (
        ALL ( Transactions );
        Transactions[Date] <= MAX ( Transactions[Date] )
    )
)

My goal is to have a running total for each source, the missing/null values showing the value from the previous period and the total starting individually from each source from the period where the first transaction occured.

 

Thank you for the help!

1 ACCEPTED SOLUTION

@Anonymous I don't have the time atm to download and play around, but I would suggest with what you describe that you add a date table. This will give you the continuous dates and is likely the reason you are seeing issues with the calculation because you don't have a seemless date range that the date dimension provides.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Thanks for the hint!

 

I have been modifying the date table so that the first date is equal to the first date in the transaction table and the last date is equal to the last date in the transaction table. Now the running total works, starts for the individual first transaction and shows no gaps.

 

BalanceChange = SUM(Transactions[Credit Amount]) - SUM(Transactions[Debit Amount])

RunningTotal = 
CALCULATE (
   SUM(Transactions[Credit Amount]) - SUM(Transactions[Debit Amount]);
    FILTER (
        ALL ( Transactions );
        Date[Date] <= MAX (Date[Date] )
    )
)

The only addition i would like to see, and have been trying to get to yesterday is to get the running total to stop after the last transaction date has been reached for that individual source. I don't seems to get the Transaction date column into the filter of the measure above.

@Anonymous I don't have the time atm to download and play around, but I would suggest with what you describe that you add a date table. This will give you the continuous dates and is likely the reason you are seeing issues with the calculation because you don't have a seemless date range that the date dimension provides.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

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.