CODEWITHSUNDEEP
powerbidax
StackTrace
StackTrace

Reputation: 9416

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

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

Upvotes: 4

Views: 32877

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40224

You can use the FORMAT function for this.

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

Upvotes: 15

Marcus
Marcus

Reputation: 541

What you are looking for in DAX is an IF statement.

Assuming this is part of a calculated column in your dataset, then something like below is what you are looking for. Where if your month number is less then 10, then append a 0 between year and month, else just append year and month.

Expiry_MonthYear_Sorter  = IF ([Expiry_Date].[MonthNo] < 10 ),
                               [Expiry_Date].[Year]  & "0" &[Expiry_Date].[MonthNo],
                               [Expiry_Date].[Year]  & [Expiry_Date].[MonthNo]
)

Upvotes: 1

Related Questions