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 ha...
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.

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.