DATEDIFF with a condition

[android1]

Hi,

How would I modify the below to give me Weeks of Employment in one column?

I want to use Weeks of  Employement 1 but if they have a [DateLeft] then I need to use Weeks of Employment 2

Weeks of  Employement 1 = DATEDIFF(vw_PivotVisitsInvoiced[Carer_StartDate],TODAY(),WEEK)

Weeks of Employment 2 = DATEDIFF(FIRSTDATE(vw_PivotVisitsInvoiced[Carer_StartDate]),

      LASTDATE(CarersList[DateLeft]) ,

      WEEK

 )

[v-yulgu-msft · Accepted solution]

Hi @android1,

Suppose there existing a relationship between 'vw_PivotVisitsInvoiced' and 'CarersList' based on employee ID. If so, you can use below formula to add the DateLeft into 'vw_PivotVisitsInvoiced'.

CarerLastDate = RELATED(CarersList[DateLeft])

Then, create the following calculated column:

Weeks of Employment= IF([CarerLastDate]=BLANK(),

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

                           DATEDIFF(

                               FIRSTDATE(vw_PivotVisitsInvoiced[Carer_StartDate]),

                               LASTDATE(CarersList[DateLeft]) ,

                               WEEK

))

Best regards,
Yuliana Gu

[android1]

@v-yulgu-msft A big thanks for this. Does exactly what I needed.

Can't thank you enough.

[nickchobotar]

@android1

The answer to your question was alread presented here:

https://community.powerbi.com/t5/Desktop/Count-Number-of-Weeks-with-a-condition/m-p/377017#M171322

Please try to avoid problem duplication in the future.

Nick -