Date formula does not work, but if I input single digits it works

[Anonymous]

In a measure I am using, I create a date as follows:

DATE(
    YEAR(MAX('Calendar create Date'[Date]))-1,
    MONTH(MAX('Calendar create Date'[Date])),
    DAY(MAX('Calendar create Date'[Date]))
)

Which is supposed to give me the max of the calendar date of last year. This works, tried both in cards and in other measures. However, when I put it on a trend line graph it doesn't work and gives me this error:

I don't understand what I'm doing wrong here. If I do say DATE(2021,10,17) it works perfectly fine with all the rest of the measure calculations as well

[FreemanZ]

hi @Anonymous 

try to write the measure like this:

MaxDateLY = 
VAR _MaxYear = 
YEAR( 
    MAXX(
        ALL('Calendar create Date'[Date]),
        'Calendar create Date'[Date]
    )
)
RETURN
MAXX(
    FILTER(
        ALL('Calendar create Date'[Date]), 
        YEAR('Calendar create Date'[Date]) =_MaxYear-1 
    ),
    'Calendar create Date'[Date]
)

tried and it worked like this:

sample data:

it is supposed to ignore any filter context, like:

[ale_BI]

Thank you so much for this. It works perfectly, and I'm trying to understand especially why you decided to use MAXX here, can you help me understand your thought process?

[johnt75]

Use Performance Analyzer to copy the query generated by Power BI for the visual and paste it into DAX Studio. Change the SUMMARIZECOLUMNS to add a new column which shows 

MAX('Calendar create Date'[Date])

and run the query, sorted by the new column in descending order. That should show if you have unexpectedly high values in the date.

[Anonymous]

I don't see any suspiciously high values. The formula still works like this

[johnt75]

can you share a PBIX file with any confidential info removed ?

[Anonymous]

Hmm the PBIX is way too big, and I can't possibly cancel out all the confidential info without leaving some behind. I'm afraid I can't share it