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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Titatovenaar2
Helper III
Helper III

DAX: Filter on Multiple Date Columns

Hi there,

 

I have a table ('Mutations') that has two columns with different dates ('Begindate' and 'Enddate'), and I want to be able to select a period (for instance the month April 2021) and that it then shows ALL records related to Begindate = April 2021, and Enddate= April 2021.

 

For that I know I need multiple steps, and the first is to integrate both columns into a single table with all values:

Mutations_Period = UNION(VALUES(Mutations[Begindate]);VALUES(Mutations[Enddate]))

 

Next I need 2 measures, but I only know how to do this with string values, and not with dates and especially not a date range:

Mutation_Period = FIRSTNONBLANK('Mutations_Period '[Begindate],1)
Mutation_Period_Filter = 
IF(
    ISERROR(SEARCH([Measure],FIRSTNONBLANK(Mutaties[Begindatum],1))) = FALSE() ||
    ISERROR(SEARCH([Measure],FIRSTNONBLANK(Mutaties[Einddatum],1))) = FALSE() 
    ,0,1)

Of which I put the last filter on '0' on the visual.

 

Any suggestions how to make this work on a date range?

 

Kind regards,

Igor

 

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

Hi, @Titatovenaar2 

According to your description and sample pbix file, I can roughly understand your requirement, I think you can just use this measure to apply as the visual filter of the table chart:

 

Flag =

VAR _selectedmonth =

    MONTH ( MAX ( 'Mutations_Period'[StartDate] ) )

RETURN

    IF (

        MONTH ( MAX ( 'Mutations'[StartDate] ) ) = _selectedmonth

            || MONTH ( MAX ( 'Mutations'[EndDate] ) ) = _selectedmonth,

        1,

        0

    )

 

Then go to the table chart to apply the visual filter like this:

v-robertq-msft_0-1621588740490.png

 

And you can get what you want.

You can download my test pbix file below

 

Thank you very much!

 

Best Regards,

Community Support Team _Robert Qin

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

3 REPLIES 3
v-robertq-msft
Community Support
Community Support

Hi, @Titatovenaar2 

According to your description and sample pbix file, I can roughly understand your requirement, I think you can just use this measure to apply as the visual filter of the table chart:

 

Flag =

VAR _selectedmonth =

    MONTH ( MAX ( 'Mutations_Period'[StartDate] ) )

RETURN

    IF (

        MONTH ( MAX ( 'Mutations'[StartDate] ) ) = _selectedmonth

            || MONTH ( MAX ( 'Mutations'[EndDate] ) ) = _selectedmonth,

        1,

        0

    )

 

Then go to the table chart to apply the visual filter like this:

v-robertq-msft_0-1621588740490.png

 

And you can get what you want.

You can download my test pbix file below

 

Thank you very much!

 

Best Regards,

Community Support Team _Robert Qin

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

amitchandak
Super User
Super User

@Titatovenaar2 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

Check this blog on how to deal with two dates using date table

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Thanks for this blog post, it's quite interesting, but I have to say that I can't easily grasp why they make the choices they do. I am experimenting with it now, but not so lucky yet.

 

Meanwhile, this is the file in which I have it all. Basically If I select 'April', I want 'April' from both column 'StartDate' and 'EndDate'.

 

MultipleDataFilter.pbix 

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! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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