ENDOFMONTH + Most Recent Date

[sergiod04]

Hello, 

I have a table which brings back values for the last date of the month and the most recent date available in the data set. For example, if the most recent date in the data set is 8/10/2020, it will bring back: 

                         06/30/2020         07/31/2020         8/10/2020 

Dimension 1          345                     450                    465

Dimension 2         1,350                   756                    820

im doing it with the following formula: 

Measure = CALCULATE (SUM(VALUE), FILTER(DATE_TABLE,DATE_TABLE[Date] = ENDOFMONTH(DATE_TABLE[Date])))

The problem is that if i select 8/8/2020 as a filter for my max date (not the most recent date in my calendar table), i get this: 

                         06/30/2020         07/31/2020       

Dimension 1          345                     450                   

Dimension 2         1,350                   756               

where the desired product would be this: 

                         06/30/2020         07/31/2020         8/8/2020 

Dimension 1          345                     450                    365

Dimension 2         1,350                   756                    210

help?

[amitchandak]

@sergiod04 , try like

Measure = CALCULATE (SUM(VALUE), FILTER(DATE_TABLE,DATE_TABLE[Date] = max(DATE_TABLE[Date])))

or
Measure = CALCULATE (SUM(VALUE), FILTER(DATE_TABLE,DATE_TABLE[Date] = lastdate(DATE_TABLE[Date])))

[sergiod04]

Hi @amitchandak , 

Thanks for responding. With both the MAX and LASTDATE, it is returning all dates, not just the last day of the month + the most recent one. 😕 

end of month was doing the trick of returning only the last day of the month + the last date when i didnt have any date filters applied. the problem is when i apply a date filter that does not land on an end of month. 

i appreciate your help. 

[amitchandak · Accepted solution]

@sergiod04  Try like

Measure = CALCULATE (lastnonblankvalue(DATE_TABLE[Date],SUM(VALUE))), based on grouping it will choose date. Take month year on axis/row/column

[sergiod04]

This will work! thank you!