Forum Discussion
Solved
Multiplication with Previous Values
Excel File: Link Hello, Could you please help me write the following DAX Querry? I want to create a measure to calculate values highlighted blue in the predicted-CO2 emission column using the...
sam_hoccane
Your wishes are my command 😉 Here is the workable solution exactly as you wish
https://we.tl/t-bo914kjbfJPredicted_Measure_4 = VAR CurrentEmission = SUM ( Data[Emission] ) VAR CurrentMonth = SELECTEDVALUE ( data[Year_Month] ) VAR CurrentForcast = SELECTEDVALUE ( Data[forcast] ) VAR PreviousMonths = FILTER ( ALLSELECTED ( Data ), data[Year_Month] < CurrentMonth ) VAR PreviousN_Months = FILTER ( PreviousMonths, Data[forcast] = "N" ) VAR PreviousN_Month = MAXX ( PreviousN_Months, data[Year_Month] ) VAR PreviousN_Emission = SUMX ( FILTER ( PreviousN_Months, data[Year_Month] = PreviousN_Month ), Data[Emission] ) VAR YearMonthOnly = DISTINCT ( SELECTCOLUMNS ( PreviousMonths, "@YearMonth", Data[Year_Month] ) ) VAR NumberOfY_Months = COUNTROWS ( FILTER ( YearMonthOnly, [@YearMonth] > PreviousN_Month && [@YearMonth] <= CurrentMonth ) ) + 1 VAR Result = IF ( CurrentForcast = "N", CurrentEmission, PreviousN_Emission * POWER ( 1.15, NumberOfY_Months ) ) RETURN Result
Hi sam_hoccane
Here is a sample file with the solution https://we.tl/t-cRyZP69iRO
For calculated column:
Predicted =
VAR CurrentEmission = Sheet1[CO2 Emission]
VAR CurrentMonth = Sheet1[Month]
VAR PreviousMonths = FILTER ( Sheet1, Sheet1[Month] < CurrentMonth )
VAR PreviousN_Months = FILTER ( PreviousMonths, Sheet1[forecast] = "N" )
VAR PreviousN_Month = MAXX ( PreviousN_Months, Sheet1[Month] )
VAR PreviousN_Emission = MAXX ( FILTER ( PreviousN_Months, Sheet1[Month] = PreviousN_Month ), Sheet1[CO2 Emission] )
VAR NumberOfY_Months = COUNTROWS ( FILTER ( PreviousMonths, Sheet1[Month] > PreviousN_Month && Sheet1[Month] <= CurrentMonth ) ) + 1
VAR Result =
IF (
Sheet1[forecast] = "N",
Sheet1[CO2 Emission],
PreviousN_Emission * POWER ( 1.15, NumberOfY_Months )
)
RETURN
Result
For measure:
Predicted Measure =
VAR CurrentEmission = SELECTEDVALUE ( Sheet1[CO2 Emission] )
VAR CurrentMonth = SELECTEDVALUE ( Sheet1[Month] )
VAR CurrentForcast = SELECTEDVALUE ( Sheet1[forecast] )
VAR PreviousMonths = FILTER ( ALL ( Sheet1 ), Sheet1[Month] < CurrentMonth )
VAR PreviousN_Months = FILTER ( PreviousMonths, Sheet1[forecast] = "N" )
VAR PreviousN_Month = MAXX ( PreviousN_Months, Sheet1[Month] )
VAR PreviousN_Emission = MAXX ( FILTER ( PreviousN_Months, Sheet1[Month] = PreviousN_Month ), Sheet1[CO2 Emission] )
VAR NumberOfY_Months = COUNTROWS ( FILTER ( PreviousMonths, Sheet1[Month] > PreviousN_Month && Sheet1[Month] <= CurrentMonth ) ) + 1
VAR Result =
IF (
CurrentForcast = "N",
CurrentEmission,
PreviousN_Emission * POWER ( 1.15, NumberOfY_Months )
)
RETURN
Result
sam_hoccane
Please check the updated solution in the original reply