Live During Date Filter

[wacook]

I am trying to figure out how to create a date filter that will work for my data. We have policies that go live and expire on certain days, so we like to see deals that are live during certain periods. For instance, we compare all policies that were live in Q3 '22 vs all policies that were live Q3 '21. It is quite simple, all deals that expire after September 30 and start after October 1 are live during Q4, I just can't find a way to build that into a slicer.

[wacook]

@v-rzhou-msft Thanks for this, but how would I get it to filter my exhibits and not just a count of ID's as shown above?

[v-rzhou-msft]

Hi @wacook ,

I think there should be a [Expire Date] column and a [Start Date] column in your table. Here I suggest you to create an unrelated date table to build the slicer.

Calendar = ADDCOLUMNS( CALENDARAUTO(),"Year",YEAR([Date]),"Qtr",QUARTER([Date]),"Month",MONTH([Date]))

My Sample is as below.

Measure:

Current Quarter = 
CALCULATE (
    COUNT ( 'Table'[ID] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Start Date] <= MAX ( 'Calendar'[Date] )
            && 'Table'[Expire Date] > MAX ( 'Calendar'[Date] )
    )
)

Previous Quarter = 
CALCULATE (
    COUNT ( 'Table'[ID] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Start Date] <= EOMONTH ( MAX ( 'Calendar'[Date] ), -12 )
            && 'Table'[Expire Date] > EOMONTH ( MAX ( 'Calendar'[Date] ), -12 )
    )
)

Result is as below.

Best Regards,
Rico Zhou

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