Forum Discussion

sam_hoccane's avatar
sam_hoccane
Helper I
4 years ago
Solved

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...
  • tamerj1's avatar
    tamerj1
    4 years ago

    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's avatar
tamerj1
Community Champion
4 years ago

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
    )
)
sam_hoccane's avatar
sam_hoccane
Helper I
4 years ago

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,