MAX date

[Dom87326]

Hello PBI Community, 

I have the following table with both fields in text type and filter slicer with year from dimDate table.

dimDate 1:* %DATE_KEY

I am trying to build a conditional column 'date check' in text type to return a text type value with the largest %DATE_KEY in yyyy mm 01 format. I.e. when filter is set to 2021 05 03, date check would return 2021 12 01 in text type else is null. 

I have been using below code with no luck: 

FORMAT ( LEFT ( MAX ( [%DATE_KEY] ), 6 ) & "01", "General Number" 

Thanks in advance for any ideas.

[amitchandak · Accepted solution]

@Dom87326 , Try like

new =
var _max = max(Table%DATE_KEY])
return
format(date(left(_max,4), mid(_max,5,2), right(_max,2)), "YYYY MM DD")