Filtering a Measure with Another Measure

[BrianaHop]

I am still pretty new to Power BI and am having an issue with a date filter. I am creating a Payroll report that has 2 tables. One table is week to week Payroll Data (Overtime, Hours Worked, etc.). This is refreshed weekly and has a field on every row called Week Ending to show what week each row applies to. Then I have a "Hired Employees" table that lists all new hires and their starting date. This is updated intermittently and does not have a week ending date, just the employee's start date. I am creating a dashboard that shows how much overtime each department has vs how many new hires they had in the past 15 and 30 days and have a date slicer for week ending in XX.

For the 30 days column, I need to only see people hired between 16-30 days prior to the Week Ending filter. So if someone was hired 14 days ago, they would only show under "Hired Past 15 Days" and not "Hired Past 30 Days". If they were hired 16 days ago, they would show up in the "Hired Past 30 Days" column only.

I created a date table and was able to create a measure for "Hired in the Past 15 Days from Selected Week Ending" with the following measure: 

Hired in the Past 15 Days from Selected Week Ending = CALCULATE(COUNT('HiredEmployees'[Hire_Date]),DATEADD('Date'[Date],-15,DAY))

The issue I am having is with the 30 days calculation. I thought I could create the same measure above for 30 days and add a filter so that it only calculates results if ""Hired in the Past 15 Days from Selected Week Ending"" is not Blank but its not allowing me to use a measure as a filter. This makes sense but I dont know how to work around it. 

The Report in Excel looks like this if that helps: 

Week Ending	Department	Overtime Hours	New Hire 15-days	New Hire 30-days
11/7/2020	LG	1
11/7/2020	IT
11/7/2020	HR	11.25
11/7/2020	FD	46.25
11/7/2020	CM
11/7/2020	CR
11/7/2020	CF
11/7/2020	BR	46	2
11/7/2020	SALES		1
11/7/2020	ACCT	47
11/14/2020	LG
11/14/2020	IT		2
11/14/2020	HR		1
11/14/2020	FD	48
11/14/2020	CM	25
11/14/2020	CR	2	1	1
11/14/2020	CF	1
11/14/2020	BR	0		1
11/14/2020	SALES	3
11/14/2020	ACCT	0

[v-janeyg-msft · Accepted solution]

Hello, @BrianaHop

It's a pleasure to answer for you.

According to your description, I think you can create a measure, then use it in the filter pane.

Like this:

measure =
IF (
    CALCULATE (
        COUNT ( 'HiredEmployees'[Hire_Date] ),
        DATEADD ( 'Date'[Date], -15, DAY )
    )
        = BLANK (),
    0,
    1
)

If you do not solve your problem, please feel free to ask me.

Best regards

Janey Guo

If this post helps,then consider Accepting it as the solution to help other members find it faster.