Search code examples
exceldaxpowerpivot

Cumulative sum of amortized values

I have a hard time figuring out a dax formula. Basically, I do have a Measure called "CF_1110 MEH Bestand" (short: M1110) What I need is a new Measure (My Goal/Target) that transforms the M1110 into something different. I have constructed a table that manually does what I want:

The Table

Using a pivot I have pulled the M1110 showing the value per month.

The formulas spread the values in the following months. The January value is spread over the next 12 months (1.016.972 / 12 = 84.748), the February value is spread over the next 11 months (688.636 / 11 = 62.603), etc.

The new Measure I need should do that and show me the sum per month. So the Measure should result in an equivalent of that bold "Target" column to the right.

My current solution: I load this handcrafted Table into the Data Modell via query and those have it in the data model. The issue: That solution si not really flexible or robust.

Solution

  • You need to iterate through each month up until the current one summing the measure distributed across the future months.

    Target =
CALCULATE (
    SUMX ( Table1, [CF_1110 MEH Bestand] / ( 13 - Table1[Month] ) ),
    FILTER ( ALL ( Table1[Month] ), Table1[Month] <= MAX ( Table1[Month] ) )
)