Forum Discussion

senorbol's avatar
senorbol
Frequent Visitor
2 years ago
Solved

Compound an Accumulated Value over time

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's avatar
    bhanu_gautam
    2 years ago

    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

1 Reply

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    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