How do I change a Month-Year text column to a Date column so I can have a Relative Date filter?

[rouelandrew]

So my dashboard has a column filter that is "Month", and the data inside is text which reads:

• 2023-10
• 2023-11
• 2023-12
• 2024-01
• 2024-02
• 2024-03
• 2024-04

The dashboard is only supposed to show 3 months at a time so what we've been doing is just manually changing the month filter to the most recent 3 months. I'm being asked to create a Relative Month/Date filter so that it will automatically update to the last 3 months but I can't do it since the month column is in Text format and if I change it to a Date format or use a DATEVALUE function it messes up the whole dashboard. How do I create a relative month filter or at least automate it to just keep showing the most recent 3 month data?

[v-heq-msft · Accepted solution]

Hi @rouelandrew ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

Creaet a calculate column

Date Type = DATE(LEFT('Table'[Date], 4), RIGHT(LEFT('Table'[Date], 7), 2), 1)

Apply this to the filter

The attached pbix is the result in import mode, but you can still use it in direct query mode after some testing.

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[danextian]

Hi @rouelandrew ,

I would in the query editor add another column that shows the date equivalent your month column.

I would then add a calculated to identify whether a period/month is within the last 3 months

L3M = 
ABS ( DATEDIFF ( MAX ( 'Table'[Date] ), 'Table'[Date], MONTH ) - 1 ) <= 3

[rouelandrew]

It gives me the below error if I follow these steps