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,
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.