Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 -
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |