Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Maria_Maria
Helper II
Helper II

help with DAX to filter dates

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! 

1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

Hi @Maria_Maria ,

 

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

vchenwuzmsft_0-1661838546861.gif

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.

 

View solution in original post

1 REPLY 1
v-chenwuz-msft
Community Support
Community Support

Hi @Maria_Maria ,

 

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

vchenwuzmsft_0-1661838546861.gif

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.

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.