topic Re: Multiplication with Previous Values in DAX Commands and Tips

Multiplication with Previous Values

sam_hoccane — Mon, 11 Jul 2022 21:16:36 GMT

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 formula given in the formula column.

So, for the forecast emission for July, I am taking the previous month's value ( i.e. actual emission in june=100) and multiplying it by 1.15 to get a 15% increase for July. For August, I take the forecast value of July (i.e. 100*1.15= 115) and multiply it by 1.15 to get a 15% increase ( (i.e. 115*1.15= 132).

Any given help will be appreciated.

Thanks,

Sam

Re: Multiplication with Previous Values

tamerj1 — Tue, 12 Jul 2022 14:06:06 GMT

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

Re: Multiplication with Previous Values

Dhacd — Tue, 12 Jul 2022 07:14:49 GMT

Hi sam_hoccane,

Please find another way to achieve the desired solution below,

PredictedEmissions = Var TableN = filter('Table','Table'[forecast]="N") Var MaxMonth = CALCULATE(MAX('Table'[Month]),TableN) Var LastEmission = CALCULATE(MAX('Table'[CO2 Emission]),'Table'[Month]=MaxMonth,all('Table')) Var Difference =datediff(MaxMonth,'Table'[Month],MONTH) Var Predicted = (LastEmission *power(1.15,Difference)) return if( 'Table'[forecast]="Y",Predicted,'Table'[CO2 Emission])

Screenshot Attached

If this post helps, then please consider accepting it as the solution to help the other members find it more quickly.

Regards,

Atma.

Re: Multiplication with Previous Values

tamerj1 — Tue, 12 Jul 2022 07:29:05 GMT

Dhacd
Good job!
A few issues here.
We don't know whether "N" and "Y" cycle overtime. Therefore the maximum Month with "N" might be after the first "Y" row.
If a month reading is missing from the data (not taken or not recorded for any reason) the DATEDIFF will not provide a correct result. COUNTROWS seems to be more reliable in this case.

Re: Multiplication with Previous Values

sam_hoccane — Tue, 12 Jul 2022 13:33:42 GMT

Hello tamerj1 Dhacd ,

Thankyou so much for the help.

Would it be possible to provide above querry into measure.

Re: Multiplication with Previous Values

tamerj1 — Tue, 12 Jul 2022 14:06:45 GMT

sam_hoccane
Please check the updated solution in the original reply

Re: Multiplication with Previous Values

sam_hoccane — Wed, 13 Jul 2022 14:17:33 GMT

Hello tamerj1

I tried with given measure on summarized data and, it is working fine . However, when I am try on bigger dataset ( here is example - data ) it is not producing any results. Would it possible if you take a peek of code and suggest any solutions.

Thankyou so much for helping me 🙂

Re: Multiplication with Previous Values

sam_hoccane — Wed, 13 Jul 2022 15:33:33 GMT

tamerj1

Please use this link for sample data - DATA

There was some issue in early sample.

I would really appreciate for any given help. Thankuyou 🙂

Re: Multiplication with Previous Values

tamerj1 — Wed, 13 Jul 2022 15:38:52 GMT

Hi sam_hoccane
Do you want that for each type / each manager?

Re: Multiplication with Previous Values

sam_hoccane — Wed, 13 Jul 2022 15:41:23 GMT

Yes please, I also want apply filter on Manager level and also I need without manager filter.

Re: Multiplication with Previous Values

tamerj1 — Wed, 13 Jul 2022 16:28:02 GMT

sam_hoccane
Your wishes are my command 😉 Here is the workable solution exactly as you wish
https://we.tl/t-bo914kjbfJ

Predicted_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

Re: Multiplication with Previous Values

sam_hoccane — Wed, 13 Jul 2022 21:07:47 GMT

tamerj1 thankyou so much for your help 🙂 your are my angel 🙂

I have one last request. I was trying to find cummulative emission but it was not producing the results. Would you please suggest way arround.

Predicted _Cumulative_Emission =

CALCULATE( [Predicted_measure_4] , FILTER ( ALLSELECTED(data) ,data[Date] < max (data[Date])))

Here is link to data

Re: Multiplication with Previous Values

tamerj1 — Thu, 14 Jul 2022 04:50:09 GMT

sam_hoccane
Here you go https://we.tl/t-tfEd37slkZ

Comulative Predected = VAR CurrentMonth = MAX ( data[Year_Month] ) VAR T1 = FILTER ( ALLSELECTED ( data[Year_Month] ), data[Year_Month] <= CurrentMonth ) RETURN CALCULATE ( SUMX ( T1, [Predicted_Measure_4] ), ALLEXCEPT ( data, data[Type], data[Manager] ) )