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

grazitti_sapna's avatar
grazitti_sapna
Super User
3 years ago

You can use 

Tenure = SWITCH(TRUE(),Tenure[Last Hire Date]>Tenure[Termination Date],DATEDIFF(Tenure[Hire Data],Tenure[Termination Date],DAY),Tenure[Last Hire Date]<Tenure[Termination Date],DATEDIFF(Tenure[Last Hire Date],Tenure[Termination Date],DAY))