Forum Discussion

danielgatley's avatar
danielgatley
Frequent Visitor
8 years ago
Solved

Complex Filtering with Dates

Hi All,   I'm attempting to create a point in time headcount report based on our HR data but i'm having trouble correctly filtering the data.  We have a table which is called EmployementRecord (Obv...
  • Anonymous's avatar
    Anonymous
    8 years ago

    You need a date table. Use the date field in this table as a filter in your report and then use this measure:

     

     

    Measure =
VAR selectedDate = SELECTEDVALUE(Date[Date])
RETURN
CALCULATE(
    COUNTROWS(EmployementRecord),
    FILTER(EmployementRecord,
        EmployementRecord[StartDate] <= selectedDate
            && (EmployementRecord[EndDate] >= selectedDate || ISBLANK(EmployementRecord[EndDate]))
    )
)
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi, this solution you gave works for me only when I set a one day range in my date slicer.

 

How does the SELECTEDVALUE(date[Date]) knows which value is the higher and the lower set by the slicer?

Because in the formula are used whitout difference with the "selectedDate" variable.

 

FILTER(clusterhistory, clusterhistory[initdate] <= selectedDate
&& (clusterhistory[finishdate] >= selectedDate || ISBLANK(clusterhistory[finishdate])))
 
Thanks!!
Anonymous's avatar
Anonymous
Not applicable
6 years ago

I kept trying and I found the answer of my question. If you are around here and need it. Here it is:

 

Measure =
VAR iniDate = MIN(Date[Date])
VAR endDate = MAX(Date[Date])
RETURN CALCULATE( COUNTROWS(EmployementRecord),
  FILTER(EmployementRecord,
    EmployementRecord[StartDate] <= iniDate
    && (EmployementRecord[EndDate] >= endDate || ISBLANK(EmployementRecord[EndDate]))
  )
)