Format a DD/MM/YYYY date in a measure

PaulBoyes · May 23, 2020, 5:35am

I have need to convert an accounting period into a date and it is not formatting properly.
This calculation is to get the first day of the month of the calendar date that the accounting transaction falls in. e.g. 01/mm/yyyy

The DAX I have is:

Transaction Date =
VAR vLyEndMonth = MONTH(MAX(Company[NextYearEnd]))
VAR vLyEndYear = YEAR(MAX(Company[NextYearEnd]))
VAR vTranMM = MONTH(SELECTEDVALUE(NlTransactionSummary[mm]))
VAR vTranYY = YEAR(SELECTEDVALUE(NlTransactionSummary[YY]))
VAR vYeMM = IF(vLyEndMonth + vTranMM > 12, vLyEndMonth - 12, vLyEndMonth)
VAR vYeYY = IF(vLyEndMonth + vTranMM > 12, vLyEndYear - 1, vLyEndYear)
VAR vYeDate = "01" & "/" & vYeMM & "/" & vYeYY
RETURN
vYeDate

The issue is with the month part of the date is not formatting properly. I am getting, for example, -9 for September instead of 09.

Any help greatly appreciated.

cristian.angyal · May 23, 2020, 6:02am

Hello @PaulBoyes,

I would try to add a Date Column using Power Query and use that in DAX.
Is this possible with your model?

If you liked my solution please give it a thumbs up .

If I did answer your question please mark my post as a solution .

Thank you !

Cristian

marcster_uk · May 23, 2020, 7:16am

You can use FORMAT() to format and/or return parts of a date.
STARTOFMONTH() as the name suggests, returns the first date of the month.

Measure = FORMAT(STARTOFMONTH(‘Transactions’[Date]),“MM”) //Returns 09 for September.

Measure2 = STARTOFMONTH(‘Transactions’[Date]) //Returns the first date of month for Transaction Date.

C9411010 · May 23, 2020, 2:46pm

Just to build on what @marcster_uk sent , here are some pre-defined time format

“General Date” -
Displays a date and/or time. For example, 3/12/2008 11:07:31 AM. Date display is determined by your application’s current culture value.

“Long Date” or “Medium Date” -
Displays a date according to your current culture’s long date format. For example, Wednesday, March 12, 2008.

“Short Date” -
Displays a date using your current culture’s short date format. For example, 3/12/2008.

“Long Time” -
Displays a time using your current culture’s long time format; typically includes hours, minutes, seconds. For example, 11:07:31 AM.

“Medium Time” -
Displays a time in 12 hour format. For example, 11:07 AM.

“Short Time” -
Displays a time in 24 hour format. For example, 11:07.

EnterpriseDNA · May 26, 2020, 1:10am

Hi @PaulBoyes, we’ve noticed that no response has been received from you since the 23rd of May. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!