Power BI Wildcard in SWITCH function

Question

I have a custom column at present which works great:

MonthOrder = SWITCH([DateText],"Apr-2024", 1, "May-2024", 2, "Jun-2024", 3, "Jul-2024", 4,"Aug-2024",5,"Sep-2024",6,"Oct-2024",7,"Nov-2024",8,"Dec-2024",9,"Jan-2025",10,"Feb-2025",11,"Mar-2025",12)

However, for the new year I need to make the dates into a wildcard, ie LIKE "Apr%", "May%" etc but I am not sure if this can be done. Has anyone done this before?

Sam Nseir · Accepted Answer · 2025-04-05 02:02:50Z

0

You could use the LEFT function to get the first three characters, and then switch on that:

MonthOrder = SWITCH( LEFT([DateText], 3), "Apr", 1, "May", 2, ..., "Mar", 12)

Alternatively, you could leverage the DATEVALUE function to convert the date text to a Date, then offset the months, then use the MONTH function:

MonthOrder = MONTH( EOMONTH(DATEVALUE("01-" & [DateText]), -3) )

answered Apr 5 at 2:02

Sam Nseir

13.1k2 gold badges5 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paula Morrison Apr 7 at 7:53

That looks like it could work - thank you so much

Collectives™ on Stack Overflow

Power BI Wildcard in SWITCH function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related