topic Re: Merge date with week number in DAX Commands and Tips

Merge date with week number

logan_logan — Wed, 22 May 2024 16:27:44 GMT

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" but then adding the date of that week with Week number is unable to do. The week starts from Monday-Sunday.

Re: Merge date with week number

AnalyticPulse — Wed, 22 May 2024 16:30:23 GMT

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

Commercial Real Estate Portfolio Overview by DataFreedom

Superstore Sales 2022 vs 2023: Year-on-Year Performance Comparison dashboard

Case Study: PowerBI Dashboard Developer for Sales Visualization

Case Study: Banking and Finance dashboard (Step by step guide)

Powerbi Visualisation

Re: Merge date with week number

logan_logan — Wed, 22 May 2024 16:52:33 GMT

can you please recheck this part?

VAR StartOfWeek = STARTOFWEEK('YourDateColumn', 2)

there is no such function like STARTOFWEEK

Re: Merge date with week number

Anonymous — Fri, 24 May 2024 04:32:24 GMT

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.