search

sql-bi / DaxDateTemplate

Power BI template to create a Date table in DAX
MIT License
265 stars 76 forks source link

Add Detailed Week Number #37

Open ThimiosSp opened 4 years ago

ThimiosSp commented 4 years ago

I use the Template all over my models but I would greatly appreciate a Detailed Week Number in format 'W17 - 20/04/20-26/04/20' to use in slicers. I have added a column for that already, but would be great to have this embedded.

weigh2tall commented 4 years ago

I added the following to my calendar table. It worked for our case.

At Line 774 "Calendar Week Start Label", CalendarGregorianPrefix & "W" & FORMAT ( CalWeekNumber, "00" ) & "-" & CalDate - WEEKDAY(CalDate, FirstDayOfWeek +1 )+1, At Line 1412 "Calendar WeekLabel", [Calendar Week Start Label],

Produces a value "W26-6/22/2020" for example. I think this could handle if you had your calendar setup to start on a day other than Monday because it's leveraging "FirstDayOfWeek" from the variable setup.