Forum Discussion

logan_logan's avatar
logan_logan
Icon for Helper I rankHelper I
2 years ago
Solved

Merge date with week number

Hi, I am trying to convert date into week suchway: Week number (fisrt day of that week - last day of that week) Example:     "Week-21 (20 May -26 May)" . I was able to extract week number "Week-21...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi logan_logan ,

    I create a table as you mentioned.

    Then I create a calculated column and here is the DAX code.

    Week Description =
VAR WeekNumber =
    "Week-" & WEEKNUM ( [Date], 2 )
VAR WeekStart =
    [Date] - WEEKDAY ( [Date], 2 ) + 1
VAR WeekEnd = WeekStart + 6
RETURN
    WeekNumber & " ("
        & FORMAT ( WeekStart, "dd MMM" ) & " - "
        & FORMAT ( WeekEnd, "dd MMM" ) & ")"

    Finally you will get what you want.

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AnalyticPulse's avatar
AnalyticPulse
Icon for Solution Sage rankSolution Sage
2 years ago

Hello logan_logan 
please try below dax :
Week Range =
VAR StartOfWeek = STARTOFWEEK('YourDateColumn', 2) 
VAR EndOfWeek = StartOfWeek + 6
RETURN
"Week-" & FORMAT(StartOfWeek, "ww") & " (" & FORMAT(StartOfWeek, "dd MMM") & " - " & FORMAT(EndOfWeek, "dd MMM") & ")"

 

Learn Power BI free:

https://analyticpulse.blogspot.com

Learn Power BI free

Powerbi Visualisation

 

 

logan_logan's avatar
logan_logan
Icon for Helper I rankHelper I
2 years ago

can you please recheck this part?

VAR StartOfWeek = STARTOFWEEK('YourDateColumn', 2) 

there is no such function like STARTOFWEEK