The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
What is a way to display the tenure of a person in the format of "X years, X months"? I was thinking of doing a simple DATEDIFF calculation between their hire date and today's date but from there I'm not sure how to get the DAX to simultaneously provide the number of years and months. Either year or months has to be selected when constructing that type of formula. Thanks!
Solved! Go to Solution.
hello @Anonymous,
Pretty much the same as @Anonymous...
Tenure Calculated Column =
VAR EndingDate =
IF(
Table3[termDate] = BLANK(),
TODAY(),
Table3[termDate]
)
VAR Yr = DATEDIFF(Table3[hireDate], EndingDate, YEAR)
VAR Mo = MOD(DATEDIFF(Table3[hireDate], EndingDate, MONTH),12)
VAR Tenure =
IF(
Yr > 0,
COMBINEVALUES(" ", Yr,"Years", Mo,"Months"),
COMBINEVALUES(" ", Mo,"Months")
)
RETURN Tenure
Proud to be a Super User!
hello @Anonymous,
Pretty much the same as @Anonymous...
Tenure Calculated Column =
VAR EndingDate =
IF(
Table3[termDate] = BLANK(),
TODAY(),
Table3[termDate]
)
VAR Yr = DATEDIFF(Table3[hireDate], EndingDate, YEAR)
VAR Mo = MOD(DATEDIFF(Table3[hireDate], EndingDate, MONTH),12)
VAR Tenure =
IF(
Yr > 0,
COMBINEVALUES(" ", Yr,"Years", Mo,"Months"),
COMBINEVALUES(" ", Mo,"Months")
)
RETURN Tenure
Proud to be a Super User!
Hi Chris ! Thanks for this.
I used the this DAX formula for the tenure calculation in PowerBI. But it gives wrong calculation for the Year.
For example someone who joined in 25 March 2019. Ideally it should be 3 Years and 10 Months. DAX formula gives 04 Years and 10 Months. Can someone help me correcting this formula?
@N5 -
It looks like the answer isn't as easy as you might think. Take a look at DATEDIFF, YEARFRAC – DAX Guide - SQLBI and decided what is best for your use case.
Proud to be a Super User!
Thank you @ChrisMendoza and @Anonymous ! Both work. Love having multiple ways to do things 🙂
You could so something like this. Calculate each piece separately.
For example,
Tenure Months
var years = text.from(datediff([start_Date],[enddate], year)) var months = text.from(datediff([start_Date],[enddate], month) - (datediff([start_Date],[enddate], year) *12)) years + " Years, " + months + " Months"
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
261 | |
120 | |
113 | |
83 | |
71 |