Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I have a date field, and I need to extract the week for each date.
I know I can use WEEKNUM, but the output of this function are week that don't have 7 days..
So I need to have all the weeks with 7 days, starting at the first week that is complete in a year.
How can I do this?
Solved! Go to Solution.
Thanks.
Almost worked. But it was useful to edit my code.
This is working for what I need:
-Week start on Monday
-7 Days for each week
Year&WeekNo With MondayAsFirstDay =
IF (
--If (Week=1) AND (Year of MondayOfWeek )<> (Year of the DateField), Then
WEEKNUM ( Calendar[Date];2 ) = 1 && YEAR(CalendarioIN[DateMondayofWeek])<>Year(Calendar[Date]);
YEAR ( Calendar[Date] ) - 1 & "/" & Weeknum(CalendarioIN[DateMondayofWeek])-1;
IF(
--If (Week=53) AND (Year of SundayOfWeek )<> (Year of the DateField), Then
WEEKNUM(Calendar[Date])=53 && YEAR(CalendarioIN[DateSundayofWeek])<>Year(Calendar[Date]);
YEAR(Calendar[Date]) &"/" & WeekNum(Calendar[Date])-1;
YEAR ( Calendar[Date] ) & "/" & WEEKNUM ( Calendar[Date];2 ) - 1
)
)
--Also I've created two calculated columns
DateMondayofWeek = CalendarioIN[Fecha]-Weekday(CalendarioIN[Fecha];3)
DateSundayofWeek = CalendarioIN[Fecha]-Weekday(CalendarioIN[Fecha];3) + 7
I think this should be a built-in function in PowerBi, as it is in Qlikview
Hi,
Please try these columns:
WeekDayNo = WEEKDAY('Table'[Date])
Day = DAY('Table'[Date])
Year&WeekNo =
IF (
WEEKNUM ( 'Table'[Date] ) = 1,
"Week" & " "
& YEAR ( 'Table'[Date] ) - 1 & "/" & 52,
IF (
WEEKNUM ( 'Table'[Date] ) - 1 < 10,
"Week" & " "
& YEAR ( 'Table'[Date] ) & "/0"
& WEEKNUM ( 'Table'[Date] ) - 1,
"Week" & " "
& YEAR ( 'Table'[Date] ) & "/"
& WEEKNUM ( 'Table'[Date] ) - 1
)
)
Then choose matrix visual, the result shows:
Here is my test pbix file:
Hope this helps.
Bests Regards,
Giotto
Thanks.
Almost worked. But it was useful to edit my code.
This is working for what I need:
-Week start on Monday
-7 Days for each week
Year&WeekNo With MondayAsFirstDay =
IF (
--If (Week=1) AND (Year of MondayOfWeek )<> (Year of the DateField), Then
WEEKNUM ( Calendar[Date];2 ) = 1 && YEAR(CalendarioIN[DateMondayofWeek])<>Year(Calendar[Date]);
YEAR ( Calendar[Date] ) - 1 & "/" & Weeknum(CalendarioIN[DateMondayofWeek])-1;
IF(
--If (Week=53) AND (Year of SundayOfWeek )<> (Year of the DateField), Then
WEEKNUM(Calendar[Date])=53 && YEAR(CalendarioIN[DateSundayofWeek])<>Year(Calendar[Date]);
YEAR(Calendar[Date]) &"/" & WeekNum(Calendar[Date])-1;
YEAR ( Calendar[Date] ) & "/" & WEEKNUM ( Calendar[Date];2 ) - 1
)
)
--Also I've created two calculated columns
DateMondayofWeek = CalendarioIN[Fecha]-Weekday(CalendarioIN[Fecha];3)
DateSundayofWeek = CalendarioIN[Fecha]-Weekday(CalendarioIN[Fecha];3) + 7
I think this should be a built-in function in PowerBi, as it is in Qlikview
I did something for this either here: https://community.powerbi.com/t5/Quick-Measures-Gallery/Week-Ending/m-p/389293#M120
Or in Sequential, look through the comments. I've solved this before. https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231#M116
Check out the attached file.
Thanks.
But I can't find it in the post or attached files 😕
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |