topic Re: Sum of last N data - grouped by category in DAX Commands and Tips

Sum of last N data - grouped by category

amuljono — Sun, 29 Jan 2023 01:24:21 GMT

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

Re: Sum of last N data - grouped by category

tamerj1 — Sat, 28 Jan 2023 15:38:07 GMT

Hi amuljono

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

Re: Sum of last N data - grouped by category

amuljono — Sat, 28 Jan 2023 16:47:50 GMT

tamerj1

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

thanks

Re: Sum of last N data - grouped by category

amuljono — Sat, 28 Jan 2023 16:54:29 GMT

I revise the expected result..

Re: Sum of last N data - grouped by category

tamerj1 — Sun, 29 Jan 2023 12:08:28 GMT

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

Re: Sum of last N data - grouped by category

amuljono — Wed, 01 Feb 2023 10:39:27 GMT

tamerj1 - really appreciate your help. kudos