topic Re: Calculating based on Previous Rows in DAX Commands and Tips

Calculating based on Previous Rows

sam_hoccane — Fri, 05 Aug 2022 16:20:32 GMT

Hi All,

Data---> FILE

I have following querry:

I want to create a measure that can calculate future emission (i.e. Forecast =Y) with 15 Percent of increase. To get the acquired furure results I want to take June total emission from month of August as it considered as to peak emission month. For Septemeber forecast, I would like to multiply with 15% to August Emission value. Smilarly for October forecast, I multiply 15% with Septemeber emission.

I tried with following measure but it is not giving acquired results.

Thankyou in anticipation,

Forecast Emission with 15% Increase =

VAR CurrentEmission = SUM ( Data[Emission] )

VAR CurrentMonth = SELECTEDVALUE ( data[Year_Month] )

VAR CurrentForcast = SELECTEDVALUE ( Data[Forecast] )

VAR PreviousMonths = FILTER ( ALLSELECTED ( Data ), data[Year_Month] < CurrentMonth )

VAR PreviousN_Months = FILTER ( PreviousMonths, Data[Forecast] = "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: Calculating based on Previous Rows

tamerj1 — Fri, 05 Aug 2022 17:08:52 GMT

Hi sam_hoccane
well, it wasn't easy. Please refer to the sample file wit the solution https://www.dropbox.com/t/cEbymwCwrT06CJz6

Comulative 30% Increase = SUMX ( VALUES ( data[Year_Month] ), CALCULATE ( VAR Percentage = 0.3 VAR CurrentDate = MAX ( data[Date] ) VAR CurrentForecast = SELECTEDVALUE ( data[Forecast] ) VAR CurrentEmission = SUM ( data[Emission] ) VAR SummaryTable = CALCULATETABLE ( SUMMARIZE ( Data, data[Year_Month], "@Date", MAX ( data[Date] ), "@Forcast", SELECTEDVALUE ( data[Forecast] ), "@Emission", SUM ( data[Emission] ) ), ALL ( data ) ) VAR NTable = FILTER ( SummaryTable, [@Forcast] = "N" ) VAR YTable = FILTER ( SummaryTable, [@Forcast] = "Y" ) VAR Last2NRecords = TOPN ( 2, NTable, [@Date] ) VAR PenultimateNDate = MINX ( Last2NRecords, [@Date] ) VAR PenultimateNEmission= MINX ( FILTER ( Last2NRecords, [@Date] = PenultimateNDate ), [@Emission] ) VAR YTableBefore = FILTER ( YTable, [@Date] < CurrentDate ) VAR Result = IF ( CurrentForecast = "N", CurrentEmission, PenultimateNEmission * POWER ( 1 + Percentage, COUNTROWS ( YTableBefore ) ) ) RETURN Result ) )

Re: Calculating based on Previous Rows

sam_hoccane — Fri, 05 Aug 2022 18:27:22 GMT

tamerj1 : Thankyou so much for helping me in this...

Last question if use slicer of Manager and Travel Source on measure, it is not giving desired results. how can I manuplate the above masure to satisfy this condition.

Many Thanks,

Re: Calculating based on Previous Rows

tamerj1 — Fri, 05 Aug 2022 18:35:37 GMT

Hi sam_hoccane

please try

Comulative 30% Increase = SUMX ( VALUES ( data[Year_Month] ), CALCULATE ( VAR Percentage = 0.3 VAR CurrentDate = MAX ( data[Date] ) VAR CurrentForecast = SELECTEDVALUE ( data[Forecast] ) VAR CurrentEmission = SUM ( data[Emission] ) VAR SummaryTable = CALCULATETABLE ( SUMMARIZE ( Data, data[Year_Month], "@Date", MAX ( data[Date] ), "@Forcast", SELECTEDVALUE ( data[Forecast] ), "@Emission", SUM ( data[Emission] ) ), ALLSELECTED ( data ) ) VAR NTable = FILTER ( SummaryTable, [@Forcast] = "N" ) VAR YTable = FILTER ( SummaryTable, [@Forcast] = "Y" ) VAR Last2NRecords = TOPN ( 2, NTable, [@Date] ) VAR PenultimateNDate = MINX ( Last2NRecords, [@Date] ) VAR PenultimateNEmission = MINX ( FILTER ( Last2NRecords, [@Date] = PenultimateNDate ), [@Emission] ) VAR YTableBefore = FILTER ( YTable, [@Date] < CurrentDate ) VAR Result = IF ( CurrentForecast = "N", CurrentEmission, PenultimateNEmission * POWER ( 1 + Percentage, COUNTROWS ( YTableBefore ) ) ) RETURN Result ) )

Re: Calculating based on Previous Rows

sam_hoccane — Fri, 05 Aug 2022 18:43:06 GMT

Hello tamerj1

It is not giving data for forecast months:

Re: Calculating based on Previous Rows

tamerj1 — Fri, 05 Aug 2022 19:16:34 GMT

sam_hoccane

Ok. Am on my way home. I'll fix it once I reach

Re: Calculating based on Previous Rows

tamerj1 — Fri, 05 Aug 2022 19:24:11 GMT

Hi sam_hoccane
Here is the sample file with the modification https://www.dropbox.com/t/w8M4gpY3GYZeSNKa

Comulative 30% Increase = SUMX ( VALUES ( data[Year_Month] ), CALCULATE ( VAR Percentage = 0.3 VAR CurrentDate = MAX ( data[Date] ) VAR CurrentForecast = SELECTEDVALUE ( data[Forecast] ) VAR CurrentEmission = SUM ( data[Emission] ) VAR SummaryTable = CALCULATETABLE ( SUMMARIZE ( Data, data[Year_Month], "@Date", MAX ( data[Date] ), "@Forcast", SELECTEDVALUE ( data[Forecast] ), "@Emission", SUM ( data[Emission] ) ), ALLEXCEPT ( data, data[Travel Source], data[Manager] ) ) VAR NTable = FILTER ( SummaryTable, [@Forcast] = "N" ) VAR YTable = FILTER ( SummaryTable, [@Forcast] = "Y" ) VAR Last2NRecords = TOPN ( 2, NTable, [@Date] ) VAR PenultimateNDate = MINX ( Last2NRecords, [@Date] ) VAR PenultimateNEmission= MINX ( FILTER ( Last2NRecords, [@Date] = PenultimateNDate ), [@Emission] ) VAR YTableBefore = FILTER ( YTable, [@Date] < CurrentDate ) VAR Result = IF ( CurrentForecast = "N", CurrentEmission, PenultimateNEmission * POWER ( 1 + Percentage, COUNTROWS ( YTableBefore ) ) ) RETURN Result ) )

Re: Calculating based on Previous Rows

sam_hoccane — Fri, 05 Aug 2022 22:16:23 GMT

tamerj1 Thankyou so much for the help 🙂