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,
i have a table (Ratings) with multiple values (5 Star Ratings) per day and they're cumulated, like this:
Date | Weekday | Weeknum | 5 Star Ratings |
28.02 | SUN | 9 | 1.000 |
28.02. | SUN | 9 | 1.100 |
01.03 | MON | 10 | 1.150 |
01.03 | MON | 10 | 1.160 |
02.03 | TUE | 10 | 1.200 |
03.03 | WED | 10 | 1.300 |
03.03 | WED | 10 | 1.356 |
... | |||
07.03 | SUN | 10 | 1.500 |
GOAL: I want a measure that is showing me per week the last value of the previous week. So i can calculate averages etc. with differences etc.
So like:
Current | Previous | |
Week 9 | 1.100 | ... |
Wek 10 | 1.500 | 1.100 |
Current week is easy with SUM and MAX Date.
For previous week I tried like this:
Prev.Week =
It doesn't show me anything.
Thx for your help.
Micha
Solved! Go to Solution.
@Anonymous
You can try the following formula:
Prev.Week =
CALCULATE(SUM(Ratings[5Star]),FILTER(ALL(Ratings),Ratings[Weeknum]=(MAX(Ratings[Weeknum])-1) && Ratings[Date]=(Ratings[Date]+ 7-WEEKDAY(Ratings[Date],2)) ) )
@Anonymous
You can try the following formula:
Prev.Week =
CALCULATE(SUM(Ratings[5Star]),FILTER(ALL(Ratings),Ratings[Weeknum]=(MAX(Ratings[Weeknum])-1) && Ratings[Date]=(Ratings[Date]+ 7-WEEKDAY(Ratings[Date],2)) ) )
@Anonymous , Check My WOW blog, it can help, using rank and Week/Date table
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 thx. Weekrank helped a lot thx.
I have the sum of the previous week so far:
@Anonymous
If you want the value from last day of previous week try adding this to your formula:
&& Ratings[Date]=(Ratings[Date]+ 7-WEEKDAY(Ratings[Date],2))
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 |
---|---|
95 | |
94 | |
79 | |
71 | |
64 |
User | Count |
---|---|
120 | |
105 | |
99 | |
81 | |
72 |