Add leading 0 to a column using PowerPivot Dax

Just a quick one tip today, I thought I already blogged this one but i couldn’t find it.

Sometimes you want your value to be always x chars wide. For example you want to create a yearmonth column of always 6 figures, 201010 and 201001 instead of 20101. What you can do is create a new column where you use the REPT function. This function repeats a specific string for a specific number. For example REPT(“0”,2-LEN(MONTH(Tablix1[Date]))) would give me 0 for 2 – the length of my month function. If the length of my month is 2 it wouldn’t return a 0 if it is one it would return one 0.

My year month calculated column dax would look like:

=year(Tablix1[Date]) & REPT(“0”,2-LEN(MONTH(Tablix1[Date]))) & MONTH(Tablix1[Date])

  • Another option is to utilize the FORMAT function like FORMAT(MONTH(Tablix1[Date]),”00″) would always provide you with a leading zero if the length of the month is only 1. Makes the formula for the calculated column a little smaller.

    • Kasper de Jonge

      Thx Dan looks easy 🙂