Forum Discussion
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
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:
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.
3 Replies
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
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'.
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:
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.
