topic Re: FORMAT DAX WEEKNUM in DAX Commands and Tips

FORMAT DAX WEEKNUM

jytech — Thu, 11 Jun 2020 23:39:42 GMT

Hello,

I created a Calendar Table using DAX. I currently have a Column with the Week Number with DAX written as: Week Number = WEEKNUM('Date'[Date],1). Instead of simply showing just the Week Number (i.e. 1,2,3,4...53), I would like to format it to only show "MM/YYYY" (i.e. 01/2020). Is there a way to format the WEEKNUM DAX Date Function to only show "MM/YYYY" only while not showing the actual Week Number?

Re: FORMAT DAX WEEKNUM

vivran22 — Fri, 12 Jun 2020 01:25:36 GMT

Hello jytech ,

You can use the FORMAT function

"WekNum", FORMAT([Date],"MM/YYYY")

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

Re: FORMAT DAX WEEKNUM

jytech — Fri, 12 Jun 2020 20:06:09 GMT

Hi Vivek,

Thanks for your feedback but that FORMAT function does one of two things:

If I use this FORMAT([Date],"MM/YYYY"), it groups it by month which does not give me the Week Hierarchy that I need.
If I use it like this FORMAT(WEEKNUM([Date],1),"MM/YYYY"), it gives me a year of "1900"

Re: FORMAT DAX WEEKNUM

jytech — Fri, 12 Jun 2020 20:08:34 GMT

Right now, this works:

"(" & "Week " & 1 + WEEKNUM('Date'[Date]) - WEEKNUM(STARTOFMONTH('Date'[Date])) & ") " & FORMAT('Date'[Date], "MM") & "/" & FORMAT('Date'[Date], "YYYY") and it gives me "(Week 1) 01/2018" but I can't seem to remove the "Week 1" from the output.

Re: FORMAT DAX WEEKNUM

vivran22 — Sun, 14 Jun 2020 05:12:16 GMT

jytech

Can you share few sample output you are looking for? That would help me in understanding the requirement better.

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter