Format date to show YYYY_MM (direct query). I need leading zero in Month 2020_01 for sorting

[Anonymous]

Struggling with Diret query and making a custom grouping "date" field. 

I turned the date (non-contiguous) into a Year_Month field but I need the leading 0 in the month so it sorts properly. I can't figure out FORMAT() and suspect even in this case Direct Query says No to that.

My formula:

NPD_Date_YYYY_MM = YEAR(DIM_Product[NPDDate]) & "_" & MONTH(DIM_Product[NPDDate])

Please, thank you and I give KUDOS and mark as Solved!

[mahoneypat]

This expression gets your desired column with leading zeros.

YM Format = FORMAT('Date'[Date], "YYYY_MM")

You can also address this by making a sort column with a YearMonth index column.

Regards,

Pat

[Anonymous]

@mahoneypat ,

That will not work with Direct Query. Format() is not allowed. 

[v-yingjl · Accepted solution]

Hi @Anonymous ,

You can try to create a custom column in power query like this:

=Table.AddColumn(#"Removed Other Columns", "Custom", each if 
Date.Month([FullDateAlternateKey]) < 10 then
Text.From(Date.Year([FullDateAlternateKey])) & "0" & 
Text.From(Date.Month([FullDateAlternateKey]))
else 
Text.From(Date.Year([FullDateAlternateKey])) & 
Text.From(Date.Month([FullDateAlternateKey])))

Best Regards,
Yingjie Li

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

[Anonymous]

Solved it. This seems like it would be a drag on the performance because I had to make a YEAR and MONTH column for the date field, then use IF() to bring them together. 

A more eloquent solution would be appreciated. 

Cheers!

MY SOLUTION

NPD_DateYYYMM = IF (DIM_Product[NPD_Month] < 10 ,

[NPD_Year] & "0" &[NPD_Month] ,

[NPD_Year] & [NPD_Month] )