DAX: CALCULATE AND VALUES

[PowerRon]

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.

LastDate =

var _monthkey = SELECTEDVALUE('Calendar'[MO_KEY])

var _lastday = CALCULATE(

    VALUES ('Calendar'[Date]),

    FILTER (ALL('Calendar'[End Of Month Flag], 'Calendar'[MO_KEY]),

    'Calendar'[End Of Month Flag] = "Y" && 'Calendar'[MO_KEY] = _monthkey))

RETURN _lastday

_monthkey returns the right value, but then the CALCULATE returns nothing.

What am I doing wrong??

Regards
Ron

[PowerRon]

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?

[Greg_Deckler]

@PowerRon Sure:

FirstDate = 
  VAR _Date = SELECTEDVALUE('Calendar'[Date])
  VAR _Result = DATE(YEAR(_Date), MONTH(_Date), 1)
RETURN
  _Result

[Greg_Deckler]

@PowerRon 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
  _result

[PowerRon]

Hi @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?

[Greg_Deckler · Accepted solution]

@PowerRon Oh, sorry, the first one should have been:

LastDate =
  var _monthkey = SELECTEDVALUE('Calendar'[Date])
  var _result = EOMONTH(_monthkey, 0)
RETURN
  _result

Also, 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.

[PowerRon]

Magic @Greg_Deckler 
Thnx