Forum Discussion

Decal's avatar
Decal
Icon for Helper I rankHelper I
1 year ago
Solved

Slicer using the wrong date of two dates

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)
 
 
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    Hi,

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

    • Decal's avatar
      Decal
      Icon for Helper I rankHelper I
      1 year ago

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

      PositionApproved DateFill DateAverage Time to HireTime to Hire Base
      1611/17/20236/7/2024203.00203.0
      221/1/20241/5/2025 307.0

       

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

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        1 year ago


        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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.