Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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:
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?
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.
I don't see any suspiciously high values. The formula still works like this
can you share a PBIX file with any confidential info removed ?
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
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |