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

SamInogic's avatar
SamInogic
Super User
3 years ago

Hi Anonymous ,

As per your requirement, we have created sample table with columns as you mentioned Hire Date, Last Hire Date, Termination Date.


Please refer to the below screenshot,



For calculating Tenure in Months you can create a new Column with below DAX expression,

Tenure = If('Hire Table'[Last Hire Date].[Date]> 'Hire Table'[Termination Date].[Date],DATEDIFF('Hire Table'[Hire Date].[Date],'Hire Table'[Termination Date].[Date],MONTH), DATEDIFF('Hire Table'[Last Hire Date].[Date],'Hire Table'[Termination Date].[Date],MONTH))

 

These will result as below with Tenure in Months:

 

If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.


Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at [email protected]

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/