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.
Thx Dan looks easy 🙂