Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
in 2023 for Asset-1, Category 1Y and Type X1, the calculation will be:
in 2022 for Asset-1, Category 1Y and Type X2, the calculation will be:
in 2023 for Asset-1, Category 1Y and Type X2, the calculation will be:
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:
it seems the calculation added all Sales data.
Solved! Go to 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] )
)
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] )
)
I revise the expected result..
Hi @amuljono
This calculated column is created in which table? Any relationships between the two tables?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
42 | |
39 | |
33 | |
19 | |
18 |