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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
rouelandrew
Frequent Visitor

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

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?

1 ACCEPTED SOLUTION
v-heq-msft
Community Support
Community Support

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:

vheqmsft_0-1713164104026.png

Creaet a calculate column

 

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

 

Apply this to the filter

vheqmsft_1-1713164172044.png

vheqmsft_2-1713164202343.png

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

vheqmsft_0-1713164412589.png

 

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

 



 

 

 

View solution in original post

3 REPLIES 3
v-heq-msft
Community Support
Community Support

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:

vheqmsft_0-1713164104026.png

Creaet a calculate column

 

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

 

Apply this to the filter

vheqmsft_1-1713164172044.png

vheqmsft_2-1713164202343.png

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

vheqmsft_0-1713164412589.png

 

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
Super User
Super User

Hi @rouelandrew ,

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

danextian_0-1712918843862.png

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

danextian_1-1712918912473.png

 

 










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


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

It gives me the below error if I follow these steps

rouelandrew_0-1712923868369.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.