How to create a dynamic value in a text box in PowerBI that will use today's date and display a numb

[paulin]

I would like to create a text box in PowerBI that says "I have been working at company for x years (or months)". I would like the number of years they have been with the company to update automatically.

My data currently include a column for Employee Name and a column with Start Date.

How can I calculate for this value to be displayed?

[Kaviraj11 · Accepted solution]

Hi,

you can follow these steps:

1. Create a New Column for Years of Service

YearsOfService = DATEDIFF([Start Date], TODAY(), YEAR)

2. Create a New Column for Months of Service (if needed):

MonthsOfService = DATEDIFF([Start Date], TODAY(), MONTH)

3. Create a Measure for the Text Box

ServiceText = "I have been working at the company for " & [YearsOfService] & " years."

•  

[jdbuchanan71 · Accepted solution]

If you would like to do it with a measure it would look something like this.

Tenure = 
VAR _Start = SELECTEDVALUE('YourTable'[Start Date])
    VAR _YS = YEAR(_Start)
    VAR _MS = MONTH(_Start)
    VAR _DS = DAY(_Start)
VAR _Today = TODAY()
    VAR _YT = YEAR(_Today)
    VAR _MT = MONTH(_Today)
    VAR _DT = DAY(_Today)
VAR _YA = IF ( OR ( AND ( _MS = _MT, _DS > _DT ), _MS > _MT ), -1, 0)
VAR _Years = DATEDIFF(_Start,_Today,YEAR) + _YA
VAR _Months = DATEDIFF(_Start,_Today,MONTH)
RETURN "I have been working at the company for " & IF ( _Years > 0, _Years & IF ( _Years = 1, " year.", " years." ), _Months & IF ( _Months = 1, " month.", " months.") )

[Kaviraj11 · Accepted solution]

Hi,

you can follow these steps:

1. Create a New Column for Years of Service

YearsOfService = DATEDIFF([Start Date], TODAY(), YEAR)

2. Create a New Column for Months of Service (if needed):

MonthsOfService = DATEDIFF([Start Date], TODAY(), MONTH)

3. Create a Measure for the Text Box

ServiceText = "I have been working at the company for " & [YearsOfService] & " years."

•