Filter table based on less than and greater than date value

[brian0782]

I have the following SQL which returns rows based on <= GETDATE() and >= GETDATE()

select b.umr
      ,b.inception_date
	  ,b.expiration_date
from binder b
WHERE b.inception_date<=CAST(GETDATE() AS DATE) -- GETDATE() would instead be a single list date slicer 
	AND b.expiration_date>=CAST(GETDATE() AS DATE) -- GETDATE() would instead be a single list date slicer 

How am I able to achieve this in Power BI? GETDATE() would basically be a single list date slicer

[V-lianl-msft · Accepted solution]

Hi @brian0782 ,

First create a calendar table as a slicer:

date_slicer = CALENDAR(MIN('Table'[inception_date]),MAX('Table'[expiration_date]))

and then create a measure to apply to the visual level filter

Measure = IF(SELECTEDVALUE(date_slicer[Date])>=MAX('Table'[inception_date])&&SELECTEDVALUE(date_slicer[Date])<=MAX('Table'[expiration_date]),1,0)

Enable the "Single select" option

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

[brian0782]

Hi Liang

This is almost working but was expecting a 1 in the higlighted row as it falls within the date range.

Not quite sure where I'm going wrong:

Date Filter = IF(SELECTEDVALUE('Binder Dates'[Date])>=MAX('Binding Authority'[Binder Date From])&&SELECTEDVALUE('Binder Dates'[Date])<=MAX('Binding Authority'[Binder Date To]),1,0)

[V-lianl-msft]

Hi @brian0782 ,

I guess this may be caused by aggregation or relationship. This row may correspond to more than one date. Try adding a umr column in the table visual and set slicer table as disconnected. If the problem persists, please delete the sensitive data and share a sample pbix.

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

[brian0782]

Hi, I think this is caused by a many to many bi-directional relationship between Binding Authority and Claim Amounts. We don't have any UMR records in Claim Amounts but we do in Binding Authority.

I thought a "Both" filter direction would help handle this.

I'm not able to attach a sample dashboard. I don't see an optuon to attach a file

[brian0782]

Hi

Apologies for the late reply. This looks like it might work! I'll need to implement it but I can see this working..

Will give this a go and get back to you 🙂

Thanks

[smpa01]

@brian0782 

SELECTEDVALUE(Calendar[Date])