FORMAT DAX WEEKNUM

[jytech]

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?

[vivran22]

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

[jytech]

Hi Vivek,

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

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

[jytech]

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.

[vivran22]

@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