Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
danielgatley
Frequent Visitor

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 (Obviously the data is a little more involved than this but this demonstrates the setup);

  

EmployementRecordIDEmployeeIDStartDateEndDateJob
1101/01/201726/06/2017Cleaner
2220/01/201711/07/2017IT Support
3303/01/201706/01/2017Power BI Developer
1126/06/2017NULLCEO
2211/07/2017NULLCleaner

 

To get the head count on any given day we use the following SQL;

select * from EmployementRecord Where (StartDate <= '2017-01-21') AND (EndDate >= '2017-01-21' or EndDate is null)

So I guess the question is how could I form this on PowerBI so a user can select a given date and only the headcount on that given date is returned.

 

Please forgive my lack of knowledge or understanding i'm just a systems admin trying to show the business how PowerBI can be used in the hope they will get a professional in to do the actual work.

 

Any help is appriciated.

 

Dan

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

9 REPLIES 9
shebr
Resolver III
Resolver III

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

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
Not applicable

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

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.

Anonymous
Not applicable

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
Not applicable

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

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

Hey @danielgatley

 

If you need a quick fire way to create a full date table this is a great video.

 

https://binged.it/2LNBoeE

 

Thanks

 

Shebr

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

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors