topic Re: Dax Week Calculation - Power BI in DAX Commands and Tips

Dax Week Calculation - Power BI

RenanPNog — Sun, 29 Jan 2023 17:20:48 GMT

Hey folks!

I'm having some trouble trying to calculate weeknumbers.... That's because, i have a sells time period, and i want to check how many weeks are left of that period. I've tried DATEDIFF function, and none got the result i was expecting...

I'll give an example based on one product that i have, but i noticed that it applies for the rest as well.

My first date is 26/10/2022, and last is 05/06/2023, there's 222 days in that period, which returns me 31.71428 weeks. As there isn't a 1.5 week, those decimal numbers results on 31 full weeks, and 5 days.

Here's the dax formula i'm using:

And here's my results:

The point is, i do not have 32 weeks, but the formula is rounding and returning me this unexisted additional weeknumber.
I'm expecting that from day 26/10/2022 to 01/11/2022 is week 31, 02/11/2022 to 08/11/2022 is week 30 and so on.

If i use a rounddown function, that's the result that i get:

Still not giving me the right week number, because it is expected that day 01/11/2022 is week num 31.

I'm cracking my head out to figure it out a way to solve this, and i noticed one thing.... When i get DATEDIFF on days, and then divide by 7 (to bring weeknumbers), that's what i'm getting :

My decimals results in a pattern that shows me exactly when weeknum should change (except for my first value). For example, the first result is "31.71429" and when day is 01/11/2022, result is "30.85714". But in day 02/11/2022, where i'm expecting weeknum 30, my result is "30.71429" again.... same 0.71429 decimal number.

Maybe that's the key to solve this problem (still trying over here), but to round down every decimal under or equal 0.71429, EXCEPT for my largest value, because in 31.71249 i'm expecting 31 and not 32 value on weeknumber.

Re: Dax Week Calculation - Power BI

Anonymous — Mon, 30 Jan 2023 09:07:22 GMT

Hi RenanPNog ,

Based on your DAX expression and your requirements description, you want to avoid rounding values like "30.85714" and instead round down, right? If this is the case, I recommend that you nest a round-down function RoundDown () in DAX outside when outputting the value of this week, you can refer to this official documentation.

ROUNDDOWN function (DAX) - DAX | Microsoft Learn

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Dax Week Calculation - Power BI

RenanPNog — Mon, 30 Jan 2023 21:30:08 GMT

Hi, Anonymous .

Unfortunatelly, i don't think ROUNDDOWN function will solve my issue...

whenever i use a round function, it will only cut my decimals and won't resolve it...

I've pasted another picture, for example:
Week 31 starts in 26/10, and value is 31,71429. My correct last day of week 31 is 02/11, and the final result gives me 30,71429, the next one (that i want to round down) is 30,57143.

If i set the rounddown function, it will turn this "30,57143" to 31, and that's not what i want.

I noticed this pattern, that every time it gives me this decimal "0,71429" i want to round up, other wise, round down.

E.G (expected result)
day current Value expected result

26/10 31,71429 31

27/10 31,57143 31

28/10 31,42857 31

29/10 31,28571 31

30/10 31,14286 31

31/10 31,00000 31

01/11 30,85714 31

02/11 30,71429 30

03/11 30,57143 30

04/11 30,42857 30

05/11 30,28571 30

06/11 30,14286 30

07/11 30,00000 30

08/11 29,85714 30

09/11 29,71429 29
10/11 29,57143 29

Re: Dax Week Calculation - Power BI

Payeras_BI — Tue, 31 Jan 2023 11:51:58 GMT

Hi RenanPNog ,
And why not using DATEDIFF with WEEK as interval?
I hardcoded the dates for demonstration purposes.

Re: Dax Week Calculation - Power BI

RenanPNog — Tue, 31 Jan 2023 13:57:57 GMT

Hello, J.Payeras.

The reason is because if i bring week as argument, it won't return me the expected result, check it out:

Also, please, check my top comment, where i show expected weeknum result for each day.

Thanks in advance.

Re: Dax Week Calculation - Power BI

Payeras_BI — Tue, 31 Jan 2023 15:40:55 GMT

Hi RenanPNog ,
Please share your pbix file via link.

Re: Dax Week Calculation - Power BI

Payeras_BI — Tue, 31 Jan 2023 18:09:39 GMT

Hi RenanPNog ,

Ok, I get what you mean now.

Re: Dax Week Calculation - Power BI

CBT_Dez — Wed, 29 Nov 2023 09:27:34 GMT

Hi Payeras_BI,

I'm struggling with DAX Datediff week caculation,

power bi dax No. of weeks = datediff(date(2023,11,15), date(2023,11,28), week) correctly returned result as 2, why did my power bi desktop for this formula No. of weeks = datediff(date(2023,09,06), date(2023,11,28), week) returned result of 12 when it should be 11?

I used co-pilot trying to find solution, one of the suggestions was to use datevalue; and I changed the formula to

No. of weeks = datediff(DATEVALUE("2023-09-06"), datevalue("2023-11-28"), week)

The returned result was still 12 instead of 11.

Can you suggest what went wrong with the formula please?

In order to get the correct result on my reports for now, I have changed it to

No. of weeks = INT(datediff(date(2023,09,06), date(2023,11,28), week))

Re: Dax Week Calculation - Power BI

CBT_Dez — Wed, 29 Nov 2023 09:30:44 GMT

Correction:

In order to get the correct result on my reports for now, I have changed it to

No. of weeks = INT(datediff(date(2023,09,06), date(2023,11,28), day) / 7)