Compound an Accumulated Value over time

[senorbol]

Hi All, 

Hope someone can help with this calculation I've got stuck on. I'm looking to compound some accumulated values in my data over the rest of the year. I have data for June, August and July and a calendar table built from June to December. I want to write a dax calculation for compounded values for each month through to December.

My data source in Power BI is just month and saving in the screenshot below.

I've used the following logic in excel (screenshot below)

Coulmn A = Month
Column B = Saving
Column C = Cumulative
Column D = Compounded

Cumulative is calculated as follows Current Column in B + Previous Column in C. Example =C2+B3
Compounded is calculated as follows Current Column in C + Previous Column in D. Example = =D2+C3

The table belows shows the calculations. Is there anyway to write a dax calc to work out the Compounded column?

[bhanu_gautam · Accepted solution]

@senorbol , To get this first create a date table if you does not have

Calendar =
ADDCOLUMNS (
CALENDAR (DATE(2024, 6, 1), DATE(2024, 12, 31)),
"Month", FORMAT([Date], "MMMM"),
"MonthNumber", MONTH([Date])
)

Then you can create a cumulative column using the following DAX:

dax
CumulativeSaving =
CALCULATE (
SUM (SavingsData[Saving]),
FILTER (
ALL (SavingsData),
SavingsData[MonthNumber] <= EARLIER (SavingsData[MonthNumber])
)
)

Then create a compound column

CompoundedSaving =
VAR CurrentMonth = SavingsData[MonthNumber]
VAR PreviousCompounded =
CALCULATE (
MAX (SavingsData[CompoundedSaving]),
FILTER (
ALL (SavingsData),
SavingsData[MonthNumber] < CurrentMonth
)
)
RETURN
IF (
ISBLANK (PreviousCompounded),
SavingsData[CumulativeSaving],
SavingsData[CumulativeSaving] + PreviousCompounded
)

Ensure that your SavingsData table has a relationship with the Calendar table on the Month column. This will allow you to use the MonthNumber for sorting and calculations