Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
09-03-2021 11:01 AM
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