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 period...
Need Help
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.