Dax Week Calculation - Power BI

[RenanPNog]

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.

[Payeras_BI]

Hi @RenanPNog ,

Ok, I get what you mean now.

[Payeras_BI]

Hi @RenanPNog ,
Please share your pbix file via link.

[Payeras_BI]

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

[CBT_Dez]

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

[CBT_Dez]

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)

[RenanPNog]

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.

[v-tangjie-msft]

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. 

[RenanPNog]

Hi, @v-tangjie-msft .

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