Forum Discussion
Helper IVHow to Append current data that refreshes to a static data or historical data> Any calculation?
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
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.
2 Replies
- amitchandak
Super User
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-data-to-it-using-incremental-refresh/
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/ 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.
