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
% CHANGE = CALCULATE(SUM('MEE TSR_DATA_Weekly'[tsrw_Sum_of_VALUE]),FILTER('MEE TSR_DATA_Weekly','MEE TSR_DATA_Weekly'[Year] = 2020)) / CALCULATE(SUM('MEE TSR_DATA_Weekly'[tsrw_Sum_of_VALUE]),FILTER('MEE TSR_DATA_Weekly','MEE TSR_DATA_Weekly'[Year] = 2019)) - 1
But since my column has to be filtered ( atm manually ) to latest week '202033' it shows wrong numbers again ( I'm guessing its the percentage difference between whole years worth of data [2020] and [2019] not each week seperately.
So far only way I got correct value was by using Qucik measure "Percentage difference from filtered value" on Base value I used my Sum_of_Value column and on filter value I used my Weeknr column and picked 201933. Now when I filter to show only week 202033 on my matrix visual the data is correct for year 2020 but this way I can't add year 2019. I made a duplicate of the table and filtered out every data up until 201933 and did the same quick measure as before but with filter 201833 I was able to get correct 2019 data as well but I can only show this in 2 different visuals so I hid 1 under other and made it look like it was all together but they're not and also this way its not dynamic and I have to change settings everytime new weeks data comes in.
Solved! Go to Solution.
@Anonymous , refer my week vs week blog for this. It uses rank concept for that
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
Example
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
@Anonymous , refer my week vs week blog for this. It uses rank concept for that
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
Example
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Thank you
This worked and I got my data correctly working now as it should 🙂
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 |
---|---|
104 | |
96 | |
79 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |