Create a Measure which Filters on a specific date range

[Carrhill]

Hi,

I need to create a Filter Measure to show the Item IDs which were opened during a specified date range (30 Jan 2021 - 28 Jan 2022).

Here's what I have so far but now I'm stuck...Please help!

Opened_In_FY22 = CALCULATE(sum('Table'[Item ID]),FILTER('Table', 'Table'[Opened Date] ????

Thanks!

[Samarth_18 · Accepted solution]

Hi @Carrhill ,

If its static date range then you can try this:-

Opened_In_FY22 =
CALCULATE (
    SUM ( 'Table'[Item ID] ),
    FILTER (
        'Table',
        'Table'[Opened Date] >= DATE ( 2021, 01, 30 )
            && 'Table'[Opened Date] <= DATE ( 2022, 01, 28 )
    )
)

or if its coming from date slicer then

Opened_In_FY22 =
var min_date = min(date_slicer[date])
var max_date = max(date_slicer[date])
CALCULATE (
    SUM ( 'Table'[Item ID] ),
    FILTER (
        'Table',
        'Table'[Opened Date] >= min_date 
            && 'Table'[Opened Date] <= max_date 
    )
)

Thanks,

Samarth

[Syk]

Instead of FILTER use the DATESBETWEEN function. If you need specific dates you can just type them with some double quotes!

CALCULATE(SUM('Table'[Item ID], DATESBETWEEN('Table'[Date],MAX('Table'[Opened Date]),TODAY()))