Forum Discussion
Dax Week Calculation - Power BI
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.
8 Replies
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.
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 result26/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
Hi RenanPNog ,
And why not using DATEDIFF with WEEK as interval?
I hardcoded the dates for demonstration purposes.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.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 toNo. of weeks = INT(datediff(date(2023,09,06), date(2023,11,28), week))- Correction:In order to get the correct result on my reports for now, I have changed it toNo. of weeks = INT(datediff(date(2023,09,06), date(2023,11,28), day) / 7)
Hi RenanPNog ,
Please share your pbix file via link.
