cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors