Forum Discussion

wacook's avatar
wacook
Frequent Visitor
3 years ago

Live During Date Filter

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.

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.

  • wacook's avatar
    wacook
    Frequent Visitor
    3 years ago

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