Solved: Re: Dynamic Date Field

Anonymous · ‎07-26-2021

I have a coworker that created a date field in a dashboard (see below). Based on today's date the field would read 2021-6. My concern is how will this field appear once we reach January 2022. How do I change this dax formula so that when we are in January 2022 the field shows December 2021?

month p1 Display = YEAR([month p0 DATE]) & "-" & Month([month p0 DATE]) - 1

m3tr01d · ‎07-26-2021

Hello @Anonymous,

You're right, the code for your month Display wouldn't return the correct result for January 2022.
You can create a measure with this code :

VAR _LastMonth = EOMONTH( TODAY(), -1)
VAR _Formatted = FORMAT( _LastMonth, "YYYY-MM")
RETURN _Formatted

Example :
If today's date is 2022-01-06,
The first variable will return 2021-12-31
The second variable will format the result to show 2021-12

You can also change the format to "MMM YYYY" if you want.

View solution in original post

Anonymous · ‎08-02-2021

This worked! Thank you for your help!

m3tr01d · ‎07-26-2021

Hello @Anonymous,

You're right, the code for your month Display wouldn't return the correct result for January 2022.
You can create a measure with this code :

VAR _LastMonth = EOMONTH( TODAY(), -1)
VAR _Formatted = FORMAT( _LastMonth, "YYYY-MM")
RETURN _Formatted

Example :
If today's date is 2022-01-06,
The first variable will return 2021-12-31
The second variable will format the result to show 2021-12

You can also change the format to "MMM YYYY" if you want.