help with DAX to filter dates

[Maria_Maria]

Hi!

I have a data set with a date column, category, and sum. The data is daily sum for each category for the whole year. 

I need a measure with the logic like this:

if max chosen date on slider exists in all previous moths, then filter out this date of all previuos months, otherwise filter out the end of every month. 

The user needs to see the same date, as he choses, for every months. E.g. if the user chooses May 14, he will see jan 14, Feb14, March 14, April 14 and May 14.

But there is 31 that does not exist in every month, so if , e.g. Jul 31 is chosen, the measure should return the end of every month. 

I have written the first part of the logic above. 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, so I am trying to figure out how to include the end of months logic.

Any help wiould be appreciated! 

[v-chenwuz-msft · Accepted solution]

Hi @Maria_Maria ,

 if the user chooses May 14, he will see jan 14, Feb14, March 14, April 14 and May 14.

Measure:

Measure =
VAR _s =
    MAX ( 'CALENDAR'[Date] )
VAR _sy =
    YEAR ( _s )
VAR _sm =
    MONTH ( _s )
VAR _sd =
    DAY ( _s )
VAR _date =
    CALCULATETABLE (
        VALUES ( 'Table'[Date] ),
        FILTER (
            FILTER ( 'table', [Date] < _s ),
            IF (
                MONTH ( DATE ( _sy, MONTH ( [Date] ), _sd ) ) <> MONTH ( [Date] ),
                [Date] = EOMONTH ( [Date], 0 ),
                MONTH ( [Date] ) <= _sm
                    && DAY ( [Date] ) = _sd
            )
        )
    )
RETURN
    IF ( SELECTEDVALUE ( 'table'[date] ) IN _date, 1, 0 )

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.