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,
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?
Solved! Go to Solution.
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 -
I see.
Something like this ?
IF(
ISBLANK(vw_PivotVisitsInvoiced[DateLeft],
Wks of Employment,
Wks of Employment 2
)
Hi @android1
Have you tried replacing TODAY() with [DateLeft] ?
DATEDIFF(vw_PivotVisitsInvoiced[Carer_StartDate], [DateLeft] ,WEEK)
Nick
I have but columns in the Carerslist table are not available to use. This is my relationship between the two tables.
Give this one a try. You do not need RELATED() function here.
DATEDIFF(
FIRSTDATE(vw_PivotVisitsInvoiced[Carer_StartDate]),
LASTDATE(CareList([DateLeft]) ,
WEEK
)
It's not letting me choose columns from Carerlist table.
Also, I'd need to accomodate for people who have no DateLeft.
Still the same unfortunately.
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 -
Oh, works perfectly that. Thank you.
Now my problem is those with no DateLeft. Their Wks Of employment is blank.
What's your ultimate goal? How do you want to see your data ?
Do you want to see both currently employed and also the folks that have already left in a single column ?
I'd like to see Wks of Employment in one column yes. If that's doable.
Try adding together two instances of DATEIFF() like so DATEIFF(logic for employed) + DATEIFF(logic for those who left). Otherwise, you have an options to create two separate columns using two measures.
N -
Added both as Wks of Employment 3.
I'd need it to use the original Wks of Employment = DATEDIFF(vw_PivotVisitsInvoiced[Carer_StartDate],TODAY(),WEEK)
but if they have and EndDate then use
Wks of Employment 2 DATEDIFF(vw_PivotVisitsInvoiced[Carer_StartDate],PivotVisitsInvoiced[DateLeft] ,WEEK)
Wks of Employment 3 = vw_PivotVisitsInvoiced[Wks of Employment] + vw_PivotVisitsInvoiced[Wks of Employment 2]
I'd like to be able to show all in one column. So if there is no DateLeft then use the first calc column but if there is then use the second one. Like combining into an If statement.
I see.
Something like this ?
IF(
ISBLANK(vw_PivotVisitsInvoiced[DateLeft],
Wks of Employment,
Wks of Employment 2
)
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |