Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Time between dates using three columns

Hi experts,   I have the following data:     I want to create a calculated column (either in PQ or DAX - whatever is most suitable) to calculate tenure in months based on Last Hire Date. H...
Need Help
Show and Tell
  • Arul's avatar
    Arul
    3 years ago

    Anonymous ,

    try this DAX formula,

    Tenure in Months = 
IF (
    'Table'[Last Hire Date] > 'Table'[Termination Date],
    DATEDIFF ( 'Table'[Hire Date], 'Table'[Termination Date], MONTH ),
    DATEDIFF ( 'Table'[Last Hire Date], 'Table'[Termination Date], MONTH )
)

    Thanks,

    Arul

vicky_'s avatar
vicky_
Super User
3 years ago

For DAX:

Tenure = 
var useDate= MAX([LastHireDate], [TerminationDate])

return DATEDIFF([HireDate], useDate, DAY)

It will be something very similar in PowerQuery, but unfortunately I'm less familiar with that. Hope that helps!