Recursive Calculation

[nico.swartz]

Good Day,

I require some assistance with developing a DAX measure for the following scenario:

I want to do something similar to Cummulative totals but the totals are reducing. So as you can see in the image below:

• I have a certain cost of Production/sales (Say $100)
• Certain projects get implemented and these projects realize a certain % saving (Projects implemented yearly - only looking at yearly savings - at multiple sites and currently there are over a hundred projects)
• This means that the cost of production after Project 1 (P1) (10% saving) was implemented is now $ 90
• Project 2 (P2) now realize a saving of 5%. This saving however is now on 90 (100 - 10%) and not on the 100
• So the cost of production is now $ 85.50 after P2 gets implemented (90 * 95% )

How do I calculate the Cost after saving per project dynamically? Do I calculate this similar to
Cumulative totals but subtracting this saving every time?

[Melissa]

Hi @nico.swartz,

I think Power Query is better suited for this job, using List.Generate

First add an Index from 0

And then a Custom Column, with this logic.

myFunction = Table.AddColumn(AddIndex, "Calc Costs after Saving", each
    let
        pCost = AddIndex{0}[Production Cost] * AddIndex{0}[#"(1-%Saved)"],
        iList = List.Buffer (AddIndex[Index]),
        pList = List.Buffer (AddIndex[#"(1-%Saved)"]),
        myList = List.Generate(
            ()=> [pCost = pCost, i = 0, y = List.Max(iList) ],
            each [i] <= [y],
            each [pCost = [pCost] * pList{[i]+1}, i = [i]+1, y = [y] ]
        ),
        CreateList = Table.FromRecords(myList){[Index]}[pCost]
    in
        CreateList, type number )

Here’s my sample file. eDNA - Recursive Calculation.pbix (21.8 KB)
I hope this is helpful.

[nico.swartz]

Thanks, Melissa for your feedback. Looks like it can be a solution.

So you think it would be a better solution if I should Generate this list in the query editor rather than using a dax measure?

[Melissa]

As far as I know recursive formulas may have a pattern that can be derived but you cannot recursively self-reference a column in DAX.

After some further exploration I came up with this pattern.

Costs after Saving (Measure) = 
VAR vTable =
    ADDCOLUMNS( 'Sample',
        "@Value", PRODUCTX( 
            FILTER( ALL('Sample'),
                'Sample'[Index] <= EARLIER( 'Sample'[Index] )
            ),  'Sample'[(1-%Saved)]
        )* 'Sample'[Production Cost]
    )
RETURN

SUMX( vTable, [@Value] )

I hope this is helpful.

[nico.swartz]

Thank you so much. Sure I can make this work.