Forum Discussion

Carrhill's avatar
Carrhill
Helper I
4 years ago
Solved

Create a Measure which Filters on a specific date range

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!

Need Help
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    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

2 Replies

  • Syk's avatar
    Syk
    Resident Rockstar
    4 years ago

    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()))

     

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    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