filter dates with measure

[Maria_Maria]

I have a data set with a date column, category, and sum.

I need to show only the same date in every month as the date chosen, so that if a user chooses 12.08 then the visual shows 

- 12.07

- 12.06

- 12.05

- etc.

I created a measure with max date:

max_date_month = CALCULATE(max(table[date]), ALLEXCEPT(table, table[date],table[category]))

and a measure that chooses the same day in every month:

day_reset_month = if(day(min(table[date])) = day([max_date_month]) , 1, 0)

then placed day_reset_month on visual filter and set the value = 1

it works on all dates correctly except the last days on months, as the measure can't find 31 in several month and 30, 29 in February. 

I am trying to fugure out how to include something like if max_date_month = end of month then last date of every month = 1, 0

and also what to do about 29 and 30 in February

This is what I need in a measure:

Here is some sample data and 2 outcomes that I get with my measures. The outcomes are correct in all cases, exept if chosen the end of month. https://file.io/a0IejO06CvQB

thank you for any ideas!

[amitchandak]

@Maria_Maria , Try one of the two

max_date_month = CALCULATE(max(table[date]), Filter(all(table), eomonth(table[date],0) = eomonth(max(table[date]),0) && table[category] = max(table[category]) ))

or

max_date_month = CALCULATE(max(table[date]), Filter(allselected(table), eomonth(table[date],0) = eomonth(max(table[date]),0) && table[category] = max(table[category]) ))

[Maria_Maria]

Hi @amitchandak 

Thank you, but it eliminates the initial functionality - to chose the same dates in every month. Now it returns only last dates of months which is not what I am after

[amitchandak]

@Maria_Maria , Sorry, seems like I did not get it correctly.

Please share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Maria_Maria]

@amitchandak 

here you go https://file.io/a0IejO06CvQB

sample data, and the outcomes I get with my measures: 2 are correct, 1 is wrong