Forum Discussion
DATEDIFF with a condition
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
)
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
3 Replies
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 Guv-yulgu-msft A big thanks for this. Does exactly what I needed.
Can't thank you enough.
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 -
