Date Format Showdown! United States vs. The World 09-03-2021 11:01 AM

[Greg_Deckler]

Answering questions on the forums sometimes leads to copying data in date formats that are not in United States date formatting. Now, normally I solve this in Power Query doing a split and then a recombination of values to get a United States date format. But, thought it would be interesting to have a DAX solution as well.

TheWorldToUS = 
    VAR __Separator = "/"
    VAR __Date = MAX([TheWorld])
    VAR __Sep1 = SEARCH(__Separator,__Date,,1)
    VAR __Sep2 = SEARCH(__Separator,__Date,__Sep1+1,1)
    VAR __Day = LEFT(__Date,__Sep1 - 1)
    VAR __Month = MID(__Date,__Sep1+1,__Sep2 - __Sep1 - 1)
    VAR __Year = RIGHT(__Date,LEN(__Date) - __Sep2)
    VAR __YearFinal = IF(LEN(__Year&"")=2,__Year+2000,__Year)
RETURN
    DATE(__YearFinal,__Month,__Day)

There is also this variant:

TheWorldToUS 2 = 
    VAR __Separator = "/"
    VAR __Date = MAX([TheWorld])
    VAR __DateText = SUBSTITUTE(__Date,__Separator,"|")
    VAR __Table = 
        ADDCOLUMNS(
            GENERATESERIES(1,3,1),
            "__DayPart",PATHITEM(__DateText,[Value],TEXT)
        )
    VAR __Day = MAXX(FILTER(__Table,[Value]=1),[__DayPart])
    VAR __Month = MAXX(FILTER(__Table,[Value]=2),[__DayPart])
    VAR __Year = MAXX(FILTER(__Table,[Value]=3),[__DayPart])
    VAR __YearFinal = IF(LEN(__Year&"")=2,__Year+2000,__Year)
RETURN
    DATE(__YearFinal,__Month,__Day)

The PBIX contains a column version as well as a USToTheWorld version which really just flips the Day and Month calculations.

eyJrIjoiNzhhMDAyYTYtM2IyMS00ZjY5LTgwOGYtZTViZjFiN2I1ZGFiIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9