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 -