Measure to recalculate a series of values based on selected inputs

[JOM88]

Apologies if this is very simple. I have spent the best part of a day trying and failing to create a simple calculation. Thank you in advance whomever can help.

I have 2 tables of data:

• Raw_df which has 3 columns GRP (name), Period (integers 1 to 40) and DF which is a number.
• decay_table which has 3 columns GRP (name), decay.start (integer 1 to 40) and decay.factor (number).

In Power BI, I have slicers to select the GRP and decay.start. I have created relationships between GRP and between Period & decay.start.

What i am looking for is to calculate a new value (DF_adjusted) where:

• DF_adjusted(Period) = DF(Period) if Period < decay.start
• DF_adjusted(Period) = DF(Period - 1) * decay.factor if Period >= decay.start
  
  I have been able to create this very quickly in excel with dummy numbers but cannot for the life of me get it to work in Power bi.

[Bibiano_Geraldo]

Hi @JOM88 ,

Create a measure wih this DAX:

DF_Adjusted =
VAR SelectedDecayStart = SELECTEDVALUE(decay_table[decay.start])
VAR SelectedDecayFactor = SELECTEDVALUE(decay_table[decay.factor])
VAR CurrentPeriod = MAX(raw_df[Period])
VAR CurrentDF = SUM(raw_df[DF])
VAR PrevDF =
    CALCULATE(
        SUM(raw_df[DF]),
        FILTER(
            ALL(raw_df),
            raw_df[GRP] = MAX(raw_df[GRP]) && raw_df[Period] = CurrentPeriod - 1
        )
    )
RETURN
    IF(
        CurrentPeriod < SelectedDecayStart,
        CurrentDF,
        PrevDF * SelectedDecayFactor
    )

[JOM88]

Apologies, your solution works for what I wrote. But i realised i made a mistake in my original request. 

The tweak I need is

• CurrentPeriod < SelectedDecayStart use Current DF (as you wrote)
• CurrentPeriod = SelectedDecayStart use PrevDF * SelectedDecayFactor (I can adjust what you wrote to do this)
• CurrentPeriod > SelectedDecayStart use DF_Adjusted(period -1) * SelectedDecayFactor.
  
  
  

I tried to adapt your code using the approach you wrote, but I dont think i can reference the previous value in the same measure. Can you help again? Thank you so much

DF_Adjusted = 
VAR SelectedDecayStart = SELECTEDVALUE(decay_table[decay.start])
VAR SelectedDecayFactor = SELECTEDVALUE(decay_table[decay.factor])
VAR CurrentPeriod = MAX(raw_df[Period])
VAR CurrentDF = SUM(raw_df[DF])
VAR PrevDF_raw =
    CALCULATE(
        SUM(raw_df[DF]),
        FILTER(
            ALL(raw_df),
            raw_df[GRP] = MAX(raw_df[GRP]) && raw_df[Period] = CurrentPeriod - 1
        )
    )
VAR PrevDF_adj = 
    CALCULATE(
        SUM(raw_df[DF_Adjusted]),
        FILTER(
            ALL(raw_df),
            raw_df[GRP] = MAX(raw_df[GRP]) && raw_df[Period] = CurrentPeriod - 1
        )
    )
RETURN
    IF(
        CurrentPeriod < SelectedDecayStart,
        CurrentDF,
        IF(
            CurrentPeriod = SelectedDecayStart,
            PrevDF_raw * SelectedDecayFactor,
            
    ))

[Anonymous · Accepted solution]

Hi @JOM88 ,

Thanks for Bibiano_Geraldo's reply!
And @JOM88 , about

but I dont think i can reference the previous value in the same measure

You are right, DAX cannot handle the problem of loops, you can convert your problem into a math problem to solve it, please try the following DAX:

DF_Adjusted = 
VAR SelectedDecayStart = SELECTEDVALUE(decay_table[decay.start])
VAR SelectedDecayFactor = SELECTEDVALUE(decay_table[decay.factor])
VAR CurrentPeriod = MAX(raw_df[Period])
VAR CurrentDF = SUM(raw_df[DF])
VAR PrevDF_raw =
    CALCULATE(
        SUM(raw_df[DF]),
        FILTER(
            ALL(raw_df),
            raw_df[Period] = SelectedDecayStart - 1
        )
    )
RETURN
IF(
    CurrentPeriod < SelectedDecayStart,
    CurrentDF,
    IF(
        CurrentPeriod = SelectedDecayStart,
        PrevDF_raw * SelectedDecayFactor,
        PrevDF_raw * POWER( SelectedDecayFactor , CurrentPeriod - ( SelectedDecayStart - 1 ) )
    )
)

And the final output is as below:

Here I have extended the number of decimal places a lot so that you can see the actual result instead of the rounded result. You can adjust the number of decimal places displayed by yourself.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JOM88]

Thank you everyone for your help and the final solution works perfectly
Good to know i cannot reference values as i would in excel (would have innevitably tried this again numerous times).

[bhanu_gautam]

@JOM88 Ensure you have relationships between the GRP columns of Raw_df and decay_table, and between the Period column of Raw_df and the decay.start column of decay_table

DF_adjusted =
VAR SelectedDecayStart = SELECTEDVALUE(decay_table[decay.start])
VAR SelectedDecayFactor = SELECTEDVALUE(decay_table[decay.factor])
RETURN
SUMX(
Raw_df,
IF(
Raw_df[Period] < SelectedDecayStart,
Raw_df[DF],
CALCULATE(
Raw_df[DF],
Raw_df[Period] = Raw_df[Period] - 1
) * SelectedDecayFactor
)
)

[JOM88]

the code isn't quite working. I'm getting an error in the CALCULATE(Raw_df[DF] with single value for column DF in table Raw_df cannot be determined. I've checked and for a given grp there are no duplicate Period so not sure why a value is not unique.

FYI, If i try putting min/max/sum aronud the DF then i get values for Period < decaystart but blank for anything after.

Any suggestions?

[JOM88]

Decay_table

decay.start	decay.factor
5	0.800
6	0.700
7	0.600
8	0.500
9	0.500
10	0.300

Slicer: decay.srt = 5

Raw_df

Period	DF	DF_adjusted
1	12	12
2	11	11
3	10	10
4	9	9
5	8	7.2
6	7	6.4
7	6	5.6
8	5	4.8
9	4	4
10	3	3.2