Forum Discussion

yasbos's avatar
yasbos
Resolver II
4 years ago
Solved

Unique Records Based On Employee Name

Greetings to everyone 🙂 I tried to solve this below problem in SQL (my preference), but I couldn't, so I'm now trying to solve it in DAX. There is a scenario where an employee is considered active ...
  • tamerj1's avatar
    tamerj1
    4 years ago

    HI yasbos 
    I transformed the same formula to another one working at year level only and with simple subtraction ( - 1 ) we can get the desired result. This solution is faster in terms of performance but it works only at year level not date not week not month. https://www.dropbox.com/t/zkNkoE4gdTOvgLKC

    Annual Active Employees = 
    VAR CurrentPeriod = 
        VALUES ( 'Date'[Date].[Year] )
    RETURN
        SUMX (
            VALUES ( Data[FULL_NAME] ),
            VAR StartDate = 
                YEAR ( CALCULATE ( MIN ( Data[Start_Date] ) ) )
            VAR EndaDateCheck = 
                COUNTROWS ( CALCULATETABLE ( Data, Data[End_Date] <> BLANK ( ) ) ) 
                    = COUNTROWS ( CALCULATETABLE ( Data ) )
            VAR EndDate = 
                IF ( EndaDateCheck, YEAR ( CALCULATE ( MAX ( Data[End_Date] ) ) ) - 1, YEAR ( TODAY ( ) ) )
            VAR ServicePeriod =
                GENERATESERIES ( StartDate, EndDate, 1 )
            RETURN 
                IF ( NOT ISEMPTY ( INTERSECT ( CurrentPeriod, ServicePeriod ) ), 1 )
        )