cancel
Showing results for
Did you mean:
Post Patron

Calculating number of weeks between 2 columns

Hi,

I have a calculated column which tells me the number of weeks a staff member worked for the company.

Length of Service = DATEDIFF(vw_CarersAll[StartDate],vw_CarersAll[DateLeft],WEEK)

What's tricky is that not all have a DateLeft, they are still with us.

I can cater for this by using

Length of Service = DATEDIFF(vw_CarersAll[StartDate],TODAY(),WEEK)

My problem is how do I combine the two to give me total number of weeks each work has worked for the company?

Regards,

Gerry

2 ACCEPTED SOLUTIONS
Community Champion

@android1

```Length of Service =
IF (
ISBLANK ( vw_CarersAll[DateLeft] ),
DATEDIFF ( vw_CarersAll[StartDate], TODAY (), WEEK ),
DATEDIFF ( vw_CarersAll[StartDate], vw_CarersAll[DateLeft], WEEK )
)```
Community Champion

@android1

```Length of Service =
IF (
ISBLANK ( vw_CarersAll[DateLeft] ),
IF (
TRUNC ( DIVIDE ( vw_CarersAll[StartDate], 12, 0 ) ) > 0,
TRUNC ( DIVIDE ( vw_CarersAll[StartDate], 12, 0 ) ) & " Yrs and "
& MOD ( DATEDIFF ( vw_CarersAll[StartDate], TODAY (), MONTH ), 12 )
& " mos",
MOD ( DATEDIFF ( vw_CarersAll[StartDate], TODAY (), MONTH ), 12 ) & " mos"
),
IF (
TRUNC ( DIVIDE ( vw_CarersAll[Date], 12, 0 ) ) > 0,
TRUNC ( DIVIDE ( vw_CarersAll[StartDate], 12, 0 ) ) & " Yrs and "
& MOD ( DATEDIFF ( vw_CarersAll[StartDate], vw_CarersAll[DateLeft], MONTH ), 12 )
& " mos",
MOD ( DATEDIFF ( vw_CarersAll[StartDate], vw_CarersAll[DateLeft], MONTH ), 12 )
& " mos"
)
)```
6 REPLIES 6
Community Champion

@android1

```Length of Service =
IF (
ISBLANK ( vw_CarersAll[DateLeft] ),
DATEDIFF ( vw_CarersAll[StartDate], TODAY (), WEEK ),
DATEDIFF ( vw_CarersAll[StartDate], vw_CarersAll[DateLeft], WEEK )
)```
Post Patron

Yeah, that does exactly what I needed. Thank you very much.

If I wanted to show the results in years & months instead of weeks, how would I do this?

Been struggling with Concatenate but can't get it.

Gerry

Community Champion

@android1

Okay since you are doing this in a COLUMN it will look like this...

```Length of Service =
IF (
ISBLANK ( vw_CarersAll[DateLeft] ),
DATEDIFF ( vw_CarersAll[StartDate], TODAY (), DAY ) & " days or "
& DATEDIFF ( vw_CarersAll[StartDate], TODAY (), WEEK )
& " weeks or "
& DATEDIFF ( vw_CarersAll[StartDate], TODAY (), MONTH )
& " months",
DATEDIFF ( vw_CarersAll[StartDate], vw_CarersAll[DateLeft], DAY ) & " days or "
& DATEDIFF ( vw_CarersAll[StartDate], vw_CarersAll[DateLeft], WEEK )
& " weeks or "
& DATEDIFF ( vw_CarersAll[StartDate], vw_CarersAll[DateLeft], MONTH )
& " months"
)```

You can also do a Measure wrap each Date in MIN (  )

Hope this helps!

Post Patron

Hi Sean,

Yes, that's brilliant. What I would like to show is years and months. Instead of having 995 days or 142 weeks or 33 months, I would like to display this as 2 years and 9 months.

Gerry

Community Champion

@android1

```Length of Service =
IF (
ISBLANK ( vw_CarersAll[DateLeft] ),
IF (
TRUNC ( DIVIDE ( vw_CarersAll[StartDate], 12, 0 ) ) > 0,
TRUNC ( DIVIDE ( vw_CarersAll[StartDate], 12, 0 ) ) & " Yrs and "
& MOD ( DATEDIFF ( vw_CarersAll[StartDate], TODAY (), MONTH ), 12 )
& " mos",
MOD ( DATEDIFF ( vw_CarersAll[StartDate], TODAY (), MONTH ), 12 ) & " mos"
),
IF (
TRUNC ( DIVIDE ( vw_CarersAll[Date], 12, 0 ) ) > 0,
TRUNC ( DIVIDE ( vw_CarersAll[StartDate], 12, 0 ) ) & " Yrs and "
& MOD ( DATEDIFF ( vw_CarersAll[StartDate], vw_CarersAll[DateLeft], MONTH ), 12 )
& " mos",
MOD ( DATEDIFF ( vw_CarersAll[StartDate], vw_CarersAll[DateLeft], MONTH ), 12 )
& " mos"
)
)```
Post Patron

Yes, that's perfect. Thanks again.

Announcements

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors