Forum Discussion
Day count calculated column
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 |
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
3 Replies
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
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]))RETURNIF(ISBLANK(PreviousLeftDate),BLANK(),DATEDIFF(PreviousLeftDate, CurrentJoinDate, DAY))
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.
