Forum Discussion
Solved
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_Jo...
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]
)
)
RETURN
IF(
ISBLANK(PreviousLeftDate),
BLANK(),
DATEDIFF(PreviousLeftDate, CurrentJoinDate, DAY)
)