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