Calculating number of weeks between 2 columns

[android1]

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

[Sean · Accepted solution]

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

[Sean · Accepted solution]

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

[android1]

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]

@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!

[android1]

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 · Accepted solution]

@android1

How about this?

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

[android1]

Yes, that's perfect. Thanks again.