Slicer using the wrong date of two dates

[Decal]

Hello all,

I have a Access db backend for an HR db that for the purposes of this conversation has two dates:  approved date and filled date.  All positions will have an approved date, but not all have a filled date (since not all positions are filled).  I want to have a time to hire measure that shows average time to hire by month.  Average time to hire only applies if a position has been filled, so the measure should only look at positions that are filled and subtracting from that the approved date to give me average days it took.  

Time to Hire Base =

CALCULATE(

    AVERAGEX(PowerBIPositionQ,DATEDIFF(PowerBIPositionQ[Approved Date],PowerBIPositionQ[Fill Date],DAY))

,(PowerBIPositionQ[Fill Status]="Filled" || PowerBIPositionQ[Fill Status] = "Closed")

)

And then I built another measure to be able use it across time:

Avg Time to Hire =

CALCULATE([Time to Hire Base],

FILTER(PowerBIPositionQ,

PowerBIPositionQ[Fill Date]>=FIRSTDATE(dimDate[Date])&&PowerBIPositionQ[Fill Date]<=LASTDATE(dimDate[Date])

)

)

I have a date table, which is fine and and I've done this part many times.  Both approved date and fill date are connected to the date table with neither of them active.  

The Time to Hire Base works fine, but the Avg Time to Hire Measure doesn't seem to calculate.  The page filter is set up Year 2024 or Year 2025.  

Apologies for a poorly written problem, but I really can't wrap my head around what's causing the problem enough to put out a well thought out question.  I should note that the one that isn't working crosses years (setup as fiscal, but I don't think that is material)

[Anonymous · Accepted solution]

Hi @Decal ,

Based on the testing, create the sample table.

Then, using the following DAX formula.

Avg Time = 
CALCULATE(
    AVERAGEX(
        'PowerBIPosition',
        DATEDIFF(
            'PowerBIPosition'[Approved Date],
            'PowerBIPosition'[Fill Date],
            DAY
        )
    ),
    FILTER(PowerBIPosition, 
        PowerBIPosition[Fill Date] >= FIRSTDATE(DimDate[Date]) && PowerBIPosition[Fill Date] <= LASTDATE(DimDate[Date]) && NOT(ISBLANK('PowerBIPosition'[Fill Date])) && 'PowerBIPosition'[Fill Status] = "Filled")
)

Select 2024 year to filter the page. The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Show the problem there very clearly and also the expected result.

[Decal]

Since it's an HR file, I can't provide the whole file, but it can be looked at this way.  

Position	Approved Date	Fill Date	Average Time to Hire	Time to Hire Base
16	11/17/2023	6/7/2024	203.00	203.0
22	1/1/2024	1/5/2025		307.0

Even if I'm bypassing all of the date tables, why isn't the Avg Time to Hire calculating?

[danextian]

Is that really how your raw data is? You can post a sample data with confidential information removed. That's how most users in the forum do it.