Sum of last N data - grouped by category

[amuljono]

Hi,

I need to sum the last 5 years data, grouped by several columns. Data retrieved from 2 tables (forecast and historical) which has same data structures but different date range. Forecast table need to get historical data from Historical table.

Below are the data, 

Forecast:

Historical:

The expected result (updated):

i.e. in 2022 for Asset-1, Category 1Y and Type X1, the calculation will be:

• 2022 (type X1) sales: 45 + 2021 sales (type H): 20 + 2020 sales (type H): 15 + 2019 sales (type H): 10 + 2018 sales (type H): 5. Total summation is 95

in 2023 for Asset-1, Category 1Y and Type X1, the calculation will be:

• 2023 (type X1) sales: 50 + 2022 sales (type X1): 45 + 2021 sales (type H): 20 + 2020 sales (type H): 15 + 2019 sales (type H): 10. Total summation is 140

in 2022 for Asset-1, Category 1Y and Type X2, the calculation will be:

• 2022 (type X2) sales: 65 + 2021 sales (type H): 20 + 2020 sales (type H): 15 + 2019 sales (type H): 10 + 2018 sales (type H): 5. Total summation is 115

in 2023 for Asset-1, Category 1Y and Type X2, the calculation will be:

• 2023 (type X2) sales: 70+ 2022 sales (type X1): 65 + 2021 sales (type H): 20 + 2020 sales (type H): 15 + 2019 sales (type H): 10. Total summation is 180

So basically only Asset, Category and Year will be considered in the Historical table. For Forecast table, Asset, Category, Year and Type should be considered.

I append the 2 tables then create new colum:

Last5Y = SUMMARIZE(

TOPN(5, GROUPBY(FILTER('Sales profile', 'Sales profile'[Year]-4 && 'Sales profile'[Year]<='Sales profile'[Year]),'Sales profile'[Asset], 'Sales profile'[Category]),'Sales profile'[Year],ASC), "sum", SUM('Sales profile'[Sales])

)

However, the result is not as per expected.

 it seems the calculation added all Sales data.

Very appreicate any guidance around this.

thanks

[tamerj1 · Accepted solution]

Hi @amuljono 
Please refer to attached sample file with the solution

Updated = 
ADDCOLUMNS ( 
    Forecast,
    "Last 5 Years Sales",
    VAR T1 = 
        SELECTCOLUMNS ( 
            CALCULATETABLE ( 
                Forecast, 
                ALLEXCEPT ( Forecast, Forecast[Asset], Forecast[Category], Forecast[Type] )
            ),
            "Year", Forecast[Year],
            "Sales", Forecast[Sales]
        )
    VAR T2 = 
        SELECTCOLUMNS ( 
            FILTER ( 
                Historical,
                Historical[Asset] = Forecast[Asset]
                    && Historical[Category] = Forecast[Category]
            ),
            "Year", Historical[Year],
            "Sales", Historical[Sales]
        )
    VAR T3 = FILTER ( UNION ( T1, T2 ), [Year] <= EARLIER ( [Year] ) )
    VAR T4 = TOPN ( 5, T3, [Year] )
    RETURN
        SUMX ( T4, [Sales] )
)

2nd Option

Updated 2 = 
ADDCOLUMNS ( 
    Forecast,
    "Last 5 Years Sales",
    VAR T1 = 
        CALCULATETABLE ( 
            Forecast, 
            ALLEXCEPT ( Forecast, Forecast[Asset], Forecast[Category], Forecast[Type] )
        )
    VAR T2 = 
        FILTER ( 
            Historical,
            Historical[Asset] = Forecast[Asset]
                && Historical[Category] = Forecast[Category]
        )
    VAR T3 = FILTER ( UNION ( T1, T2 ), [Year] <= EARLIER ( [Year] ) )
    VAR T4 = TOPN ( 5, T3, [Year] )
    RETURN
        SUMX ( T4, [Sales] )
)

[amuljono]

@tamerj1 - really appreciate your help. kudos

[amuljono]

I revise the expected result..  

[amuljono]

@tamerj1 

the calculated column is created in the new table created from append queries.

thanks

[tamerj1]

Hi @amuljono 

This calculated column is created in which table? Any relationships between the two tables?