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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
nexaframe
Frequent Visitor

How to calculate Running Total from a summarized dataset with StartDate and MonthlySavings

Hello,

i have a very simple data-set but I am struggling extremly on how to tackle it.

I would like to calculate various measures but I am extremely struggling with all of them as the dataset is basically a summary and not a raw-data set with actual monthly values.

 

SampleData:

IDImplementationDateMonthlySavingsMonthsSinceImplementation
115-Aug-22106
220-Oct-22154
312-Dec-2252

 

I linked my dataset to a Date-Table.

And calculated: NewSavingsByDate=CALCULATE(SUM(Data[MonthlySavings]),USERELATIONSHIP('Calendar'[Date],Data[ImplementationDate]))

 

I also added a column: MonthsSinceImplementation = DATEDIFF(Data[ImplementationDate],TODAY(),MONTH)

 

With this I wanted to calculate the Total Savings = CALCULATE(SUM([MonthlySavings]*SUM([MonthsSinceImplementation].

This works per row, but not for the full data, as it then gives me "(10+15+5)*(6+4+2) = 360"

I tried with "ALLEXCEPT" filter on ID but this didn't change anything on the result.

 

Further Required Calculations:

Running Total Savings should look like this:

IDAug-22Sep-22Oct-22Nov-22Dec-22Jan-23Feb-23TOTAL
1010101010101060
20001515151560
3000005510
TOTAL0101025253030130

 

Running accumulated Savings should look like this

IDAug-22Sep-22Oct-22Nov-22Dec-22Jan-23Feb-23TOTAL
1010203040506060
20001530456060
30000051010
TOTAL010204570100130130

 

I hope someone could lead me to the right direction...

Thanks in advance!

0 REPLIES 0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.