DAX to extract year and month and day and concatenate it together and substract 100

[deb_power123]

Hi All,

Can some one suggest a DAX by which  i can extract year,month and day from a date say '01-Jan-2021' from dates column and then substract 100 from it.

For eg:  I want to extract the numeric value 20210101 from the date '01-Jan-2021 and minus 100 from it using DAX query.

Expected output:

100- 20210101 = -20210001

Please suggest.

[HashamNiaz · Accepted solution]

Hi @deb_power123 !

You can create a new column based on following DAX;

NewColumn = 100 - (YEAR(Table[Date]) & LEFT("0" & MONTH(Table[Date]), 2) & LEFT("0" & DAY(Table[Date]), 2))

Hope this will resolve your issue.

Regards,

Hasham