Creating a DAX measure from a date column with YYYYMM to YYYY01MM

[Anonymous]

Hi Everyone,

I am trying to create a dax measure which takes my MonthYear column which is formatted YYYYMM and add the first day of each month to it so the new column will be YYYYDDMM format.

For e.g.

200009 -> 20000109

202004 -> 20200104

202008 -> 20200108

Right now my Year Month table is NVARCHAR /TEXT format 😞

I am bringing data from SAP HANA so my limitation is I can't use M/Power Query Transformations only DAX Measures.

[az38 · Accepted solution]

Hi @Anonymous 

try

Measure = 
var _date = MAX('Table'[Date])
RETURN
FORMAT(DATE(VALUE(LEFT(_date,4)), VALUE(RIGHT(_date,2)),1), "YYYYDDMM")