Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello!
I am in need of some help regarding calculating production and budget on a weekly basis. My client want a table where one row conists of a Week To Date number and the next colum to se how this results compares against last weeks results.
My client uses a Friday to Thursday week, so i need to calculate the results based on this kind of week.
I have created a new column i the date table for the new week numbers:
Hi @amitchandak
Thank you very much
I get a result from this kind of procedure, but it seems like the result from mye production dosent add up. For friday, saturday and sunday the results have been around 220-230 and the week to date shows around 750. I will check the numbers closer tomorrow.
But i have have antoher question regarding Week to Date. The client would like to have a daily average. Is it possible to use some of the same logic to make a "days in week"-measure to divide the week to date production by?
I can also mention that i work with data up to yesterday, so i will never have the current date's data.
Again, thank you for your help
@Anonymous , make sure you have a separate week/date table. Other wise all at table level means you need to handle too many other things
You have week start date from my blog
week day = datediff([Week start date],[Date], Day)+1
WTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[week Rank]=max('Date'[week Rank]) && 'Date'[week day ] <= Max('Date'[week day ]) ))
LWTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[week Rank]=max('Date'[week Rank])-1 && 'Date'[week day ] <= Max('Date'[week day ])))
Hi Again @amitchandak and thank you for your answer.
Yes i have a separate date table. Only difference is that mye table uses the CALENDERAUTO function.
I now used the new calulation with the week day column, but i still get the same results as the previous measure. This result havent changed even though this week now have one more day of data. So something in my measures dosent add up
@Anonymous , get Friday to Thursday week from here
Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482
Create week Rank on week start date - set to Friday in this case
New columns in date table
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
measures examples
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))
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
User | Count |
---|---|
25 | |
12 | |
8 | |
7 | |
7 |
User | Count |
---|---|
25 | |
12 | |
11 | |
10 | |
6 |