Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
)
Solved! Go to 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
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
@v-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 -