Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hemantsingh
Helper V
Helper V

Any way of converting numeric month & weeks to their respective name using DAX formula??

Hi there,

 

     I need to convert numeric months & week in to their respective character names. I can surely do it in excel & sql but since i have already taken very huge amount of data into power bi model so i need to know if i can write a measure that can do the same for me.

1 ACCEPTED SOLUTION

@hemantsingh Month - no problem, Week Name? what are you expecting here? Or do you mean Day?

I'm not aware of how to replace the values with a DAX Calc... but you could create the additional calc columns. I would keep the numbers, as you will most likely want to order the MonthName and DayName by them.

 

MonthName = FORMAT([Date], "MMM")

DayName = FORMAT([Date], "DDD")


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

7 REPLIES 7

@hemantsingh Month - no problem, Week Name? what are you expecting here? Or do you mean Day?

I'm not aware of how to replace the values with a DAX Calc... but you could create the additional calc columns. I would keep the numbers, as you will most likely want to order the MonthName and DayName by them.

 

MonthName = FORMAT([Date], "MMM")

DayName = FORMAT([Date], "DDD")


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

@Seth_C_Bauer

 

I have simillar situation where my month column has integer values from 1 to 12, what formula will work for me in this case to convert 1= Jan, 2 =Feb etc?

 

@Seth_C_Bauer Thanks for the solution. 

@hemantsingh if you want a column for weeks, see this thread.

 

@Seth_C_Bauer best I've ever come up with for "week name" is the end date of the week.

 

Week = DateTable[Date] + (7-WEEKDAY(DateTable[Date]))

 

...or if you want the week to be named for its start date...

 

Week = DateTable[Date] + 1 - WEEKDAY(DateTable[Date])





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman Yeah, I was thinking in terms of an actual name rather than a returned date. However, this spawned an idea based on how I submit a form for our company, I always call it "Week Ending" with the day. So technically @hemantsingh could use your suggestions below and by just including "Week Ending" & or "Week Beginning" &  (or something to that effect) you would now have a "week name" to identify to end users which week you were in. - Good Stuff KH.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

@Seth_C_Bauer Technically I actually have two week columns, Week Of (which is the week ending formula above) and Week or sometimes Week Name, which uses this formula.*

 

Week Name = SWITCH(

TRUE(),

[Week] = TODAY() + (7-WEEKDAY(DateTable[Date])), "This Week",

[Week] = TODAY() - 7 + (7-WEEKDAY(DateTable[Date])), "Last Week",

[Week] = TODAY() + 7 + (7-WEEKDAY(DateTable[Date])), "Next Week",

[Week]

)

 

...which allows me to use that column as a slicer when I need a default selection to hold onto one of those weeks permanently, as discussed in this thread. Its data type is text of course. Week Of is date, which means I can use that column when I need a trendline. Flexibility!

 

 

 

*This is not entirely true. That whole table is created in the Query Editor, so the formula is written in a completely different language. But you could just as easily add this as a custom column in the Table Editor, and if I were to do so, the new DAX formula would be something like this. The math is sloppy here because I'm too lazy to do the necessary algebra to simplify "One week from today's date plus the formula that shifts any arbitrary date to the last date of the same week" so I'm sure someone who has finished their morning coffee could improve on my suggested formula.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Format does not seem to work in either a calculated column or measure?

 

Anyone know why this is?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.