Hi All,
I have two fact tables:
a) One stores historical sales data
b) One stores sales data for month - 1 (for example, we are in Oct at the moment. The table currently contains sales data for Sept21). We will refresh fact table B on Nov 1 to get a view of Oct and therefore, the Fact Table B will update to show sales for FY Oct 21.
This means, the historial table will then store away september data (fact table A). For reporting purposes, we want to give business a historical view as well as current period view.
As a result, i want to merge thes two tables as one. However, i need to be able to tag my date column from Fact Table B with it's fiscal period title dynamically . For example, the table currently contains YTD view up till Sept 21. I want to tag this table as 'Sept FY21/22'. Then once we finish October month, the data will refresh and i want the tag to then say 'Oct FY21/22'.
Does anyone know how to do this via Power Query. i can't use a calcualted column using DAX for this since i want the column to be visible via PQ.
Thanks in advance
