Forum Discussion
DAX: CALCULATE AND VALUES
Hi, Let me make my question simple. I have a slicer where one can choose a date. And I have a date table with, amongst others, a month-end-flag (Y for the last date of the month) and a month-key...
Anonymous Oh, sorry, the first one should have been:
LastDate = var _monthkey = SELECTEDVALUE('Calendar'[Date]) var _result = EOMONTH(_monthkey, 0) RETURN _resultAlso, the MAXX is just there to grab the value out of the table. You could also have used MINX. You might be able to use SELECTCOLUMNS instead of MINX or MAXX.
Anonymous 2 Ways of doing this:
LastDate =
var _monthkey = SELECTEDVALUE('Calendar'[MO_KEY])
var _result = EOMONTH(_monthkey, 0)
RETURN
_result
LastDate =
var _monthkey = SELECTEDVALUE('Calendar'[Date])
var _table = FILTER(ALL('Calendar'), MONTH([Date]) = MONTH(_monthkey) && YEAR([Date]) = YEAR(_monthkey) && [End of Month Flag] = "Y")
var _result = MAXX(_table, [Date])
RETURN
_resultHi Greg_Deckler
When I select 3-1-2022, your first solution gives me 31-5-1901, and the second one 31-1-2022.
So the second one works. Thnx.
Because in every month there is just one row with End-of-Month-Flag = Y, do we then still need the iterating MAXX?
Anonymous Oh, sorry, the first one should have been:
LastDate = var _monthkey = SELECTEDVALUE('Calendar'[Date]) var _result = EOMONTH(_monthkey, 0) RETURN _resultAlso, the MAXX is just there to grab the value out of the table. You could also have used MINX. You might be able to use SELECTCOLUMNS instead of MINX or MAXX.
Magic Greg_Deckler
Thnx