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]))
    )
)
danielgatley's avatar
danielgatley
Frequent Visitor
8 years ago

So I went back and looked at the data and decided to add a coulmn which takes EndDate and if null replaces it with the current date.  Doing this I can apply page level filters to specify dates and this gives me the results i'm after, so the question is how do I give the power to the user.  Given my filtering needs a single date value compare against two seperate columns (<= StartDate and >= EndDate) what is my best options for achieving this?  I did test using two slicers based on the StartDate and EndDate fields and this works when they are both set to the same date but this isn't exactly easy to use.

Anonymous's avatar
Anonymous
Not applicable
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]))
    )
)
  • danielgatley's avatar
    danielgatley
    Frequent Visitor
    8 years ago

    Thank you for the response SPG, just what I needed.

  • 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]))
        )
      )
  • BI_HCV's avatar
    BI_HCV
    Frequent Visitor
    6 years ago

    I know there is a long time ago, but I'm wondering if you have the pbix file to share?

    I tried to set my data as you wrote but doesn't works.