Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I am a newbie who's using Power BI for the first time, and find myself struggling over Week splitting when there is a year transition.
Indeed, I have a TIME table, whose columns are calculated like this:
By default, Week 1 begins the first Monday of the new year (Spanish calendar, weeks are Mon-Sun).
The problem is that with the DAX configuration above, week 52 is absorbed by January (which is incorrect, it should fall in December):
Does anyone who if/how can Power BI set a standard formula in order to consider Week 1 the one starting on January 1st, and week 52 until 31st December?
Thanks a lot for any hint regarding this topic (I haven't been able to find a related tutorial).
Regards,
Teresa
Solved! Go to Solution.
Hello @v-rongtiep-msft ,
Thanks for the advice. We found a workaround (basically filtering out January 1st 2023), but I keep the code in case the workaround has more impact than initially expected and we need to re-work on it.
Thanks a lot!
Regards,
Teresa
Hi @tere_or ,
I have created a new sample, please refer to my pbix file to see if it helps you.
Column =
IF (
'Table'[month] = 1
&& 'Table'[Weeknumber] > 10,
MINX (
FILTER (
'Table',
'Table'[Yearmonthnumber] = EARLIER ( 'Table'[Yearmonthnumber] )
),
'Table'[Yearweek]
),
IF (
'Table'[month] = 12
&& 'Table'[Weeknumber] <= 10,
MAXX (
FILTER (
'Table',
'Table'[Yearmonthnumber] = EARLIER ( 'Table'[Yearmonthnumber] )
),
'Table'[Yearweek] + 1
),
'Table'[Yearweek]
)
)
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @v-rongtiep-msft ,
Thanks for the advice. We found a workaround (basically filtering out January 1st 2023), but I keep the code in case the workaround has more impact than initially expected and we need to re-work on it.
Thanks a lot!
Regards,
Teresa
Did this help?
https://dax.guide/weeknum/
WEEKNUM ( 'Date'[Date], 2 ), -- Week start on Mon
Proud to be a Super User!
hi @tere_or
try to change all 21 to 2, in line 19 and 20 of your screenshot. It is the second argument of all WEEKNUM function.
Hi @FreemanZ ,
Thank you for the tip. Unfortunately it didn't work on this case, as it took as Week 2 the week between January 2nd - 8th (in Spain week starts on Monday). It put January 1st (2023) as week 52 by the end of Januay.
We solved it by filtering out January 1st 2023 (it is not a big deal, as it's a day with 0 results).
Thanks a lot.
Regards,
Teresa
User | Count |
---|---|
53 | |
35 | |
20 | |
15 | |
14 |
User | Count |
---|---|
94 | |
76 | |
36 | |
22 | |
18 |