topic Calculate Week to Date and vs last week with Fri-Thu week in DAX Commands and Tips

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

Anonymous — Fri, 09 Jul 2021 12:08:11 GMT

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!

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

amitchandak — Fri, 09 Jul 2021 17:31:52 GMT

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

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

Anonymous — Mon, 12 Jul 2021 17:01:58 GMT

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

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

amitchandak — Mon, 12 Jul 2021 17:49:54 GMT

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

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

Anonymous — Tue, 13 Jul 2021 07:52:01 GMT

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