Day count calculated column

[RichOB]

Hi, I posted something similar recently, but I need a different variation on it, please.

Using the table below, I'd like to get a column that shows the day count from the Date_Left to the Date_Joined for each employee. This will show how long they were away before rejoining us. Thanks.

NI_Number	Name	Title	Date_Joined	Date_Left	Days_Employed	Days_Between (Previous employment and most recent. This is the column I need)
NI112233	Dave Jones	Customer Service	01/04/2024	20/05/2024	49
NI112233	Dave Jones	Customer Service	01/06/2024	10/06/2024	9	12
NI884455	Eric Davies	Customer Service	01/07/2024	10/10/2024	101
NI884455	Eric Davies	Customer Service	20/10/2024	28/10/2024	8	10
NI009900	Lloyd Williams	Store Manager	01/04/2024	10/09/2024	90
NI009900	Lloyd Williams	Store Manager	01/10/2024	11/11/2024	41	21
NI887711	Ian Ians	Store Manager	10/05/2024	10/10/2024	153
NI887711	Ian Ians	Store Manager	01/11/2024		30	326

[v-saisrao-msft]

Hi @RichOB,

Have you had a chance to review the solution we shared by @Ritaf1983 @AnkitaaMishra? If the issue persists, feel free to reply so we can help further.

Thank you.

[Ritaf1983 · Accepted solution]

Hi @RichOB 
If you need just calculation you can use a dax formula:

DaysBetween... =

var start_ = CALCULATE(MIN('Table'[Date_Left]),ALLEXCEPT('Table','Table'[NI_Number]))

var end_ = CALCULATE(MAX('Table'[Date_Joined]),ALLEXCEPT('Table','Table'[NI_Number]))

Return end_-start_

If you need a result only at the last row on employee level the formula is :

Days_Between_LastOnly =

VAR Emp       = 'Table'[NI_Number]

VAR LastJoin  =

    CALCULATE(

        MAX('Table'[Date_Joined]),

        FILTER('Table', 'Table'[NI_Number] = Emp)

    )

VAR PrevLeft  =

    CALCULATE(

        MAX('Table'[Date_Left]),

        FILTER('Table',

            'Table'[NI_Number] = Emp &&

            'Table'[Date_Left] < LastJoin

        )

    )

VAR IsLastRow = 'Table'[Date_Joined] = LastJoin

RETURN

    IF(IsLastRow,

        DATEDIFF(PrevLeft, LastJoin, DAY),

        BLANK()

    )

The pbix with the example is attached

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

[AnkitaaMishra]

Hi @RichOB , Could you please try below DAX:

Days_Between =

VAR CurrentEmployee = Employees[NI_Number]

VAR CurrentJoinDate = Employees[Date_Joined]

VAR PreviousLeftDate =

    LOOKUPVALUE(

        Employees[Date_Left],

        Employees[NI_Number], CurrentEmployee,

        Employees[Date_Left],

        MAXX(

            FILTER(

                Employees,

                Employees[NI_Number] = CurrentEmployee &&

                Employees[Date_Left] < CurrentJoinDate

            ),

            Employees[Date_Left]

        )

    )

RETURN

IF(

    ISBLANK(PreviousLeftDate),

    BLANK(),

    DATEDIFF(PreviousLeftDate, CurrentJoinDate, DAY)

)