Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Calculate Week to Date and vs last week with Fri-Thu week

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: 

New Week Nr = WEEKNUM(Dates[Date]+2). 
 
I am not sure how to do this kind of calculations, espesically with this kind of week. I tried with this for the Week to date calculation, but the reults turned BLANK: 
 
WTD Budget = CALCULATE([Total Budget], FILTER(Dates,Dates[New Week Nr] = MAX(Dates[New Week Nr])), FILTER(Dates, Dates[Date] <= TODAY()))
 
Maybe i am way off, but i really need som tips for this kind of issue. Does anyone have experince with this?
 
Thank you in advance!  
4 REPLIES 4
Anonymous
Not applicable

 

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 ])))

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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

 

 

amitchandak
Super User
Super User

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.