Forum Discussion
Issue with start/end date filtering
I have a table with a created and expired date fields, I want to have 2 filters where the user can select the date range
the relationships are:
Date_start.date -> expired
Date_end.date -> created
when I move the "after" date_start filter, any record that is expired before the selected date should disappear
when I move the "before" end_date filter, any record that was created after the selected date should disappear
The issue is that the "after" date start filter works perfectly, but the other one does nothing! I also tried to remove the start filter or to make the start date relationships inactive, but nothing seems to change.
I can't understand how can a simple filter not filter anything!
Anyone has faces a similar issue ? TIA
Hi, Anonymous
Thanks for Ashish_Mathur and audreygerred reply. Using the same data structure as you, it seems to work fine.You can refer to their suggestions, or refer to the following dax.
Filter = VAR _startDate = MIN('Date Start 2'[DateStart]) VAR _endDate = MAX('Date End 2'[Date End]) VAR _created = SELECTEDVALUE('Distinct'[Created]) VAR _expired = SELECTEDVALUE('Distinct'[Expired]) VAR _result = IF(_created>=_startDate && _expired<=_endDate,1,0) RETURN _resultBest Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
6 Replies
Hi! This video is the one I follow when I am trying to accomplish things like you are: DAX measure with start AND end date for Power BI - YouTube
Hi,
Why create 2 date slicers? Why not a single date slicer with the between functionality.
Hi, Anonymous
Thanks for Ashish_Mathur and audreygerred reply. Using the same data structure as you, it seems to work fine.You can refer to their suggestions, or refer to the following dax.
Filter = VAR _startDate = MIN('Date Start 2'[DateStart]) VAR _endDate = MAX('Date End 2'[Date End]) VAR _created = SELECTEDVALUE('Distinct'[Created]) VAR _expired = SELECTEDVALUE('Distinct'[Expired]) VAR _result = IF(_created>=_startDate && _expired<=_endDate,1,0) RETURN _resultBest Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi, the DAX filter is great, but I'm not sure the logic is correct (probably my explanation is lacking).
Any record that is active during the timeframe should be shown, even if the expiration is after the "end date".
I think it should be:
Filter = VAR _startDate = MIN('Date Start 2'[DateStart]) VAR _endDate = MAX('Date End 2'[Date End]) VAR _created = SELECTEDVALUE('Distinct'[Created]) VAR _expired = SELECTEDVALUE('Distinct'[Expired]) VAR _result = IF(_created<=_endDate && _expired>=_startDate,1,0) RETURN _resultdoes it make sense?
I changed the result logic
