Count Number of Weeks with a condition

[android1]

Hi,

I'm using the below to give me the number of weeks someone has been employed.

Wks of Employment = DATEDIFF(vw_PivotVisitsInvoiced[Carer_StartDate],TODAY(),WEEK)

Works great however in another table CarersList there is an end date column [DateLeft].

Those still employed will have no end date. 

How do I modify the above to account for those who have an end date?

[nickchobotar · Accepted solution]

@android1

It appears to be some data quality. Here is another approach:

On your vw_PivotVisitsInvoiced table create the following calculated column:  CarerLastDate = RELATED(CarersList[DateLeft])

This will create another column populated with DateLeft values.

From here you can write another calc column:

DATEDIFF(vw_PivotVisitsInvoiced[Carer_StartDate],PivotVisitsInvoiced[DateLeft] ,WEEK)

or for a measure you got to wrap your fields in FIRSTDATE and LASTDATE

See if works.

N -

[nickchobotar]

Hi @android1

Have you tried replacing TODAY() with [DateLeft] ?

DATEDIFF(vw_PivotVisitsInvoiced[Carer_StartDate], [DateLeft] ,WEEK)

Nick

[android1]

I have but columns in the Carerslist table are not available to use. This is my relationship between the two tables.

[nickchobotar]

@android1

Give this one a try. You do not need RELATED() function here.

DATEDIFF(

      FIRSTDATE(vw_PivotVisitsInvoiced[Carer_StartDate]),

      LASTDATE(CareList([DateLeft]) ,

      WEEK

)

[android1]

It's not letting me choose columns from Carerlist table. 

Also, I'd need to accomodate for people who have no DateLeft.

[popov]

Replace CareList([DateLeft]) to CareList[DateLeft]

[android1]

Still the same unfortunately.

[nickchobotar · Accepted solution]

@android1

It appears to be some data quality. Here is another approach:

On your vw_PivotVisitsInvoiced table create the following calculated column:  CarerLastDate = RELATED(CarersList[DateLeft])

This will create another column populated with DateLeft values.

From here you can write another calc column:

DATEDIFF(vw_PivotVisitsInvoiced[Carer_StartDate],PivotVisitsInvoiced[DateLeft] ,WEEK)

or for a measure you got to wrap your fields in FIRSTDATE and LASTDATE

See if works.

N -

[android1]

Oh, works perfectly that. Thank you.

Now my problem is those with no DateLeft. Their Wks Of employment is blank. 

[nickchobotar]

@android1

What's your ultimate goal? How do you want to see your data ?

Do you want to see both currently employed and also the folks that have already left in a single column ?

[android1]

I'd like to see Wks of Employment in one column yes. If that's doable.

[nickchobotar]

@android1

Try adding together two instances of DATEIFF() like so   DATEIFF(logic for employed) + DATEIFF(logic for those who left).  Otherwise, you have an options to create two separate columns using two measures.

N -

[android1]

Added both as Wks of Employment 3.

I'd need it to use the original Wks of Employment = DATEDIFF(vw_PivotVisitsInvoiced[Carer_StartDate],TODAY(),WEEK)

but if they have and EndDate then use 

Wks of Employment 2 DATEDIFF(vw_PivotVisitsInvoiced[Carer_StartDate],PivotVisitsInvoiced[DateLeft] ,WEEK)

Wks of Employment 3 = vw_PivotVisitsInvoiced[Wks of Employment] + vw_PivotVisitsInvoiced[Wks of Employment 2]

[nickchobotar]

@android1

If all worked out for you, please accept this as a solution.

Thanks, N -

[android1]

@nickchobotar

I'd like to be able to show all in one column. So if there is no DateLeft then use the first calc column but if there is then use the second one. Like combining into an If statement.

[nickchobotar · Accepted solution]

@android1

I see.

Something like this ?

IF(

     ISBLANK(vw_PivotVisitsInvoiced[DateLeft],

     Wks of Employment,

     Wks of Employment 2

)