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.
Hello,
Is it possible to append or include the current data that refreshes to a static data which is historical?
I have herewith the link of the report.
I want the "Current FY" page FY21 CO2 table which has calculations to be included in the "Historical" page table which is a static data or just historical data.
https://drive.google.com/file/d/1KxiiTfwwqF5Q158ZOkZwAA8cKvYm1Ixg/view?usp=sharing
Below is the desired output. 2021 is coming from Raw Data Table which is YTD and data refreshes
Solved! Go to Solution.
Due to History Table doesn't contain values for 2021. Values for 2021 are in Raw Data and you need to generate them by measures. So you need to use summarize to build a real table shows like Table viusal in Current FY and use union to append two tables.
Build a calculated Table by dax code:
New Table =
VAR _T1 =
SUMMARIZE (
History,
History[Mode],
History[Fiscal Year],
History[Volume (Tonnes)],
History[Average distance (km)],
History[Tonnes-kms],
History[g CO2/ tonne-km],
History[Tonnes CO2]
)
VAR _T2 =
SUMMARIZE (
FILTER ( ALL ( 'Raw Data' ), 'Raw Data'[MODE] <> BLANK () ),
'Raw Data'[MODE],
'Raw Data'[Fiscal Year],
"Volume (Tonnes)", SUM ( 'Raw Data'[Volume (Tonnes)] ),
"Average distance (km)", [Average distance (km)],
"Tonnes-kms", [Tonnes-kms],
"g CO2/ tonne-km", MAX ( 'Raw Data'[g CO2/ tonne-km] ),
"Tonnes CO2", [Tonnes CO2]
)
RETURN
UNION ( _T1, _T2 )
Change all number type rows to whole number and result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Due to History Table doesn't contain values for 2021. Values for 2021 are in Raw Data and you need to generate them by measures. So you need to use summarize to build a real table shows like Table viusal in Current FY and use union to append two tables.
Build a calculated Table by dax code:
New Table =
VAR _T1 =
SUMMARIZE (
History,
History[Mode],
History[Fiscal Year],
History[Volume (Tonnes)],
History[Average distance (km)],
History[Tonnes-kms],
History[g CO2/ tonne-km],
History[Tonnes CO2]
)
VAR _T2 =
SUMMARIZE (
FILTER ( ALL ( 'Raw Data' ), 'Raw Data'[MODE] <> BLANK () ),
'Raw Data'[MODE],
'Raw Data'[Fiscal Year],
"Volume (Tonnes)", SUM ( 'Raw Data'[Volume (Tonnes)] ),
"Average distance (km)", [Average distance (km)],
"Tonnes-kms", [Tonnes-kms],
"g CO2/ tonne-km", MAX ( 'Raw Data'[g CO2/ tonne-km] ),
"Tonnes CO2", [Tonnes CO2]
)
RETURN
UNION ( _T1, _T2 )
Change all number type rows to whole number and result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@jovendeluna21 , One way I can think of is to have 2 tables, for one of them schedule incremental and merge these two in power bi
There is DAX append method too
https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-...
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |