Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
android1
Post Patron
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
Sean
Community Champion
Community Champion

@android1

 

How about this...

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

View solution in original post

Sean
Community Champion
Community Champion

@android1

 

How about this? Smiley Happy

 

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"
    )
)

View solution in original post

6 REPLIES 6
Sean
Community Champion
Community Champion

@android1

 

How about this...

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

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

Sean
Community Champion
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! Smiley Happy

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

Sean
Community Champion
Community Champion

@android1

 

How about this? Smiley Happy

 

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"
    )
)

Yes, that's perfect. Thanks again.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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