I have a scenario in a Power BI Matrix visual, where I have a simple measure, called SalesAmount. It is a simple sum based on a single column of a table, called FactSales.
I have a Date dimension (Calendar) table in my model called Dates2.
I have developed a matrix report as below. This report uses a WeekNumber from the Dates2 table. I have also developed another measure called SalesAmount_SWPY to get the SalesAmount measure for the same week in the previous year.
(The first of the above 2 visuals is the actual report, the second one is just to show you, how the measure for the corresponding week of the previous year is stored.)
It is important to note that the measure, SalesAmount is NOT cumulative (non-YTD), i.e. for Week-10 of a year, we do NOT need to add Week-1 to Week-10 sales, but show only Week-10 sales. But the year totals are aggregated across all the weeks for a given year.
I then develop a measure to generate the percentage change, in parallel weeks. For example, Week 1 of 2020 is compared with Week 1 of 2019, to develop the % change.
Formula for % change in Week-1 of 2020 = {(Value in Week-1 of 2020) - (Value in Week-1 of 2019)}/ (Value in Week-1 of 2019)
Formula for % change in 2020 = {(Total of all the weeks of 2020) - (Total of all the weeks of 2019)}/ (Total of all the weeks of 2019)
This works fine, but for one issue. In the year 2021, we have data only for 2 weeks. (Assume that as of today, we have completed only 2 weeks in 2021, even though we are in September)
As far as the percentage change is concerned, for the aggregated total of 2021 (the last column of the matrix on the right side), we need to compare the aggregated total of the first 2 weeks of 2021, with the corresponding total of the first two weeks of 2020.
In the visual above, I do not want to see -95.66%, but -18.75%.
Logic: we have completed only two weeks in 2021
Aggregated value in 2021 = 13
Aggregated value in 2020 = 16 (i.e. 1 + 15, for the first two weeks alone in 2020)
(Currently the 2020 value is taken as 300, which is incorrect)
Percentage change = (13-16)/16 = 18.75 % (needed in the report)
Percentage change = (13-300)/300 = -95.66 % (currently shown, incorrect)
I posted a similar question a few months back, for percentage change across months across different years:
Can someone help me solve this percentage change issue across Week Numbers, using a new measure in DAX ?
