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]))
    )
)
shebr's avatar
shebr
Icon for Resolver III rankResolver III
8 years ago

Hi danielgatley

 

There are a few things you can do.

 

You can add the 'date' field onto the report page, set it as a filter and use that as your 'day selection'.

 

You could create a flag column based on the same SQL code and set that to either 1 or 0 and use that as your filter/current day value.

 

Do you have examples of where you are with it all?

 

Thanks

 

Shebr

  • danielgatley's avatar
    danielgatley
    Frequent Visitor
    8 years ago

    Hi Shebr,

     

    Thanks for the response.  So i've adding a column to the EmployementRecord table with the following DAX;

    Hired = IF('EmployementRecord'[Status] = "Active",IF ('EmployementRecord'[StartDate] <= DATEVALUE("01/06/2018"),if('EmployementRecord'[EndDate] >= DATEVALUE("01/06/2018"),1,if(ISBLANK('EmployementRecord'[EndDate]),1,0)),0),0)

    This returns the results i'm expecting but thing I haven't worked out is how to get my "day selection" from the date field on the report.  This most likely a case of trying to run before I can crawl so apologies if the solution is something I should know already.

     

     

    Regards

     

    Dan

     

  • 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.