Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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.dat...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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
_result

     

     

    Best Regards,
    Yang
    Community Support Team

     

    If 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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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
_result

 

 

Best Regards,
Yang
Community Support Team

 

If 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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
_result

    does it make sense? 

     

    I changed the result logic

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi, Anonymous 

      Is your DAX formula returning the data you need? One quibble, if the activity is active, then the expiration date should be after the DateEnd, otherwise the DateEnd filter is meaningless.



      Best Regards,
      Yang
      Community Support Team