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.
