Filtering records DateFrom >= ReferenceDate and the ReferenceDate<=DateTo

[RKi]

Hi,

As reletavily new to Power BI I come up with the next problem (I'll try to expain it simple) :

I have a table "X" with four columns: PatientKey, Categorie, DateFrom and DateTo.

It contains data about the Categorie of the patient. For example Categorie "R" = Regular, "M"= Moved away.

So a patient can have multiple entries.

Now what I want in my dashboard is to type in a reference date and based on that date select the records where the DateFrom >= ReferenceDate and the ReferenceDate<=DateTo.

Anyone an idea?

Thx,

Rki

[v-cgao-msft · Accepted solution]

Hi @RKi ，

First you need to introduce a custom visual for the Text search slicer:

Text search slicer

Then we need create a new date table for the silcer. Since the Text search slicer accepts values of text type, here I have added a new calculated column:

Here you can also format the date to the customer's preferred format, I demonstrated here is "YYYYMMDD".

Then create a new measure for the visual‘s filter:

Measure = 
VAR __input = SELECTEDVALUE('Date'[Date])
VAR __cur_date_from = SELECTEDVALUE('Table'[DateFrom])
VAR __cur_date_to = SELECTEDVALUE('Table'[DateTo])
VAR __result = IF( ISBLANK(__input) || (__cur_date_from <= __input && __cur_date_to >= __input), 1)
RETURN
__result

result:

Best Regards,
Gao
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 -- China Power BI User Group

[RKi]

Thx for your solution. It works fine!!!

Great job