DAX formula for joining Year, Month and Day

[shaibn]

Hi,

I have a request to join three columns into one by showing a date in one column.

See attached example. I need a DAX formula as a resolution.

Please note that I have a row where the month and the date has no "0" before (e.g. Month = 6; Day = 3) where I would need to have at the expected date '06' for the Month and '03' for the Date.

Thanks,
Shai

[avk]

Try in "D2"

Please Login or Register  to view this content.

and copy paste down

[shaibn]

I am getting the result as '42069' number which is not identified as a date, also when I am selecting a date format it stays as '42069' and not as a date.

[bebo021999]

Try:

=TEXT(DATE(A2,B2,C2),"yyyymmdd")

[shaibn]

I am getting an error as I cannot create the following syntax '=TEXT(DATE' together, any other ideas?

[sandy666]

You said DAX so try: =[@[Year ]]&[@[Month ]]&[@Day] but table cannot be as range but as structured table.
or =DATE([@[Year ]],[@[Month ]],[@Day])
or =TEXT(DATE([@[Year ]],[@[Month ]],[@Day]),"yyyymmdd")

btw. I don't understand why standard excel function DATE(year,month,day) is called DAX function
Maybe Excel is Pocket Calculator now, who knows?

[avk]

Refer attach file. Formatting properly.

[xlnitwit]

Hi,

For a formatted string, the DAX might be
=[Year]&FORMAT([Month],"00")&FORMAT([Day],"00")

For an actual date you would, as previously suggested, use
=DATE([Year],[Month],[Day])
but there are no date formats built in to Power Pivot (I guess this is where you are using it) that are YYYYMMDD. If you require that format but stored as a number for some reason, you might use
=[Year]*10000+[Month]*100+[Day]