Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

How to determine which is the current record between multiple records for an employee

I need to show which record is the 'active' record when using a dynamic date slicer   No two records for an employee overlap position starts    Say I had a date with a date range of April 2012 to...
Need Help
parry2k's avatar
parry2k
Icon for Super User rankSuper User
4 years ago

Anonymous you have to add this a MEASURE not a COLUMN

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you for your help with troubleshooting, I don't think it is returning the right result: there should be a sum of hundreds of active FTE 

     

    Data facts to support:

    • Every employee only ever gets one employee number
    • Each employee can have several position records 
    • When the slicer is linked to a date table, which is filtered to cap the distinct count of position records per employee number to show the most recent entry within the date filters 
    • SUM the most current entry per an employee per the selected date parameters to determine the actual FTE in past or future months of a year

    Filter Employee = 
VAR __startDate = MIN ( DateTable[Date] )
VAR __endDate = MAX ( DateTable[Date] )
RETURN
CALCULATE (
    COUNTROWS ( PBIPND_vwPosition_Private ),

    KEEPFILTERS ( 
        PBIPND_vwPosition_Private[StartDate] >= __startDate
    ),
    KEEPFILTERS (
        PBIPND_vwPosition_Private[EndDate] <= __endDate
   ),
   KEEPFILTERS (
        NOT ISBLANK ( PBIPND_vwPosition_Private[EndDate] ) 
    )

)