Add leading 0 (zero) to month number in power BI

powerbi dax
StackTrace picture StackTrace · Oct 31, 2018 · Viewed 8k times · Source

I have the below DAX formula that is concatenating a month number to a year.
If the month number is less than 10, i want to add a leading zero to it but i'm new to DAX and i can't seem to figure out how to do it.

Expiry_MonthYear_Sorter = [Expiry_Date].[Year]  & [Expiry_Date].[MonthNo]

As an example, if the year is 2018 and the month number is 2, i want the value to be 201802.

Currently, my formula gives me 20182

Answer

Alexis Olson picture Alexis Olson · Oct 31, 2018

You can use the FORMAT function for this.

Expiry_MonthYear_Sorter = FORMAT([Expiry_Date], "YYYYMM")

Related questions

how to convert a Integer to Text value in Power BI

I am creating a calculated column in existing power BI report. the calculated column concatenates integer & text columns. I tried below query which give syntax error to me, = Number.ToText(table1.[RegionID]) & " " & table1.[RegionName] I tried some …

Read More
How to format new measure (not column) in power BI as percentage?

When I create a new measure with formula, i.e. sum(col1)/max(col2), it automatically converts the result into #.#% format. Strangely, it doesn't happen all the time; sometimes it just gives result #.## format. Any idea of how to format …

Read More
How to get month name from month number in Power BI?

I have Year number and Month Number in my data. How using DAX can I get the month name out of month number? In SSRS its very easy. But how to achieve that using DAX?

Read More