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.
The month-key for all rows in the same month is equal.
Now, if I choose for instance 5-1-2023, I want LastDate to return 31-1-2023, the last day of January.
_monthkey returns the right value, but then the CALCULATE returns nothing.
What am I doing wrong??
Regards
Ron
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.
6 Replies
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.
Hi Greg_Deckler
one more question.
You showed me how to quickly get the last day of the month when I grab a date from a slicer.
Is there also a way to, based on grabbed date from a slicer, easily get the first date of the month?Anonymous Sure:
FirstDate = VAR _Date = SELECTEDVALUE('Calendar'[Date]) VAR _Result = DATE(YEAR(_Date), MONTH(_Date), 1) RETURN _Result