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 everyone,
I'm having some issues with calculating percentages on a column, line by line. I will show the examlple I have in Excel of what I want to my PBI to look like, and hopefully you guys can help me with a better DAX expression than the one I developed as an attempt to solve my problem.
So, in my excel, I'm having data from units that I'm selling, week over week, for years 2021 and 2022. In PBI I was able to develop some matrices, with the exception of the column G; column G basically takes week 3 (2022) and subtract from week 2 (2022) and return the percentage of that subtraction.
Here's how my PBI looks like and my attempt to solve this problem with my DAX expression:
So the column I'm trynig to develop here is the "% Vs. Last Week 2022". I actually found out an expression that works! However, this expression is huge and I'm concerned that it can compromise the performance of my dashboard overall; and also takes too much time to write it down... So here's the expression:
The expression works! But as I said, the problem is that I have to include about 53 (number of weeks over the year) "IF" expressions as the percentage calculate for one value of the column "Visao_Semanal[Semana]"...
Here's the table I'm using for those matrices, named "Visao_Semanal"
To a better understanding of the columns names (they're in portuguese, sorry):
1 - Data = Date
2 - Semana = Week
3 - Data Final Semana = Last day of the week
4 - Ano = Year
5 - Data Começo Semana = First day of the week
6 - Rank Semana = Week Rank
Thank you so much guys, I'm looking forward to a better solution than the one I made!
Greetings from Brazil! 😉
Solved! Go to Solution.
Hi @Anonymous ,
Please try this code to replace your expression:
% Vs. Last week 2022 =
VAR _last =
CALCULATE (
SUM ( 'Fact table'[Sales] ),
FILTER (
ALLSELECTED ( 'Visao_Semanal' ),
[Year] = 2022
&& [Week Num]
= MAX ( 'Visao_Semanal'[Week Num] ) - 1
)
)
VAR _this =
CALCULATE (
SUM ( 'Fact table'[Sales] ),
FILTER (
ALLSELECTED ( 'Visao_Semanal' ),
[Year] = 2022
&& [Week Num] = MAX ( 'Visao_Semanal'[Week Num] )
)
)
RETURN
DIVIDE ( _this, _last ) - 1
Result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Please try this code to replace your expression:
% Vs. Last week 2022 =
VAR _last =
CALCULATE (
SUM ( 'Fact table'[Sales] ),
FILTER (
ALLSELECTED ( 'Visao_Semanal' ),
[Year] = 2022
&& [Week Num]
= MAX ( 'Visao_Semanal'[Week Num] ) - 1
)
)
VAR _this =
CALCULATE (
SUM ( 'Fact table'[Sales] ),
FILTER (
ALLSELECTED ( 'Visao_Semanal' ),
[Year] = 2022
&& [Week Num] = MAX ( 'Visao_Semanal'[Week Num] )
)
)
RETURN
DIVIDE ( _this, _last ) - 1
Result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-chenwuz-msft , thank you so much for this expression! It worked, exactly as I needed! Thanks a lot! 😉
@Anonymous , In your date table a week rank column
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
then you can have measures like
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
WTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[WeekDay]<=max('Date'[WeekDay])-1))
LWTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[WeekDay]<=max('Date'[WeekDay])-1 ))
Power BI — Week on Week and WTD
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
https://www.youtube.com/watch?v=pnAesWxYgJ8
@amitchandak , thank you so much for the support... It didn't worked well actually, however I appreciate a lot your help! Also your article you sent here was awsome to learn more about Week on Week and WTD.
Thanks!
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
42 | |
39 | |
33 | |
19 | |
18 |