Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sam_hoccane
Helper I
Helper I

Calculating based on Previous Rows

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. 

sam_hoccane_0-1659712356662.png

 

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
1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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
    )
)

View solution in original post

7 REPLIES 7
tamerj1
Super User
Super User

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
    )
)

@tamerj1  Thankyou so much for the help 🙂 

tamerj1
Super User
Super User

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
    )
)

Hello @tamerj1 

 

It is not giving data for forecast months: 

 

sam_hoccane_1-1659724980655.png

 

@sam_hoccane 

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

tamerj1
Super User
Super User

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
    )
)

@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. 

 

sam_hoccane_1-1659723986154.png

 

 

Many Thanks,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors