Extracting Month and Year from a short date, display result at MM/YYYY

[KHouseknecht]

I have a handfull of tables with short dates (MM/DD/YYYY) displayed. I need to be able to connect and filter them by just month and year (MM/YYYY). I've tried a few solutiuons and keep running into the problem of only getting the month and year as (Month spelled out, YYYY) or (MMYYYY) and unable to format it as a date correctly.

I feel like this shouldnt be as hard as I've made it somehow. Any help is appreciated,

Thank you!

[Elena_Kalina · Accepted solution]

Hi @KHouseknecht 

There are several straightforward ways to extract and display month/year in MM/YYYY format from a date column. Here are the best solutions:

Solution 1: Create a Calculated Column

MonthYear = 
FORMAT([YourDateColumn], "MM/YYYY")

Solution 2: Use Power Query

1. In Power Query Editor, select your date column

2. Go to "Add Column" > "Date" > "Month" > "Year"

3. Create a custom column with:

= Text.PadStart(Text.From([Month]), 2, "0") & "/" & Text.From([Year])

Solution 3: Create a Date Dimension Table

1. Create a date table with all dates in your range

2. Add columns:

MonthYear = FORMAT([Date], "MM/YYYY") MonthYearSort = YEAR([Date]) * 100 + MONTH([Date]) // For proper sorting