Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

7 Days for each Week, Starting at the first whole week of yeark

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?

Weeks.png 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

4 REPLIES 4
v-gizhi-msft
Community Support
Community Support

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:

1.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Bests Regards,

Giotto

 

Anonymous
Not applicable

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

Greg_Deckler
Super User
Super User

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. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thanks.

But I can't find it in the post or attached files 😕

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.