Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi
i want help to calute the period column in the timesheet, which can be found in the absence period tabel, how do i do that
Timesheets
Employee | Date | Period |
John | 3-1-2019 | Short period |
John | 4-1-2020 | Long period |
Chris | 8-1-2019 | Short period |
Chris | 15-1-2019 | Medium period |
Absence period
Employee | Period | Start Date | End Date |
John | Short period | 1-1-2019 | 3-1-2019 |
John | Long period | 4-1-2020 | 29-1-2020 |
Chris | Short period | 6-1-2019 | 9-1-2019 |
Christ | Medium period | 12-1-2020 | 20-1-2019 |
The solution I can think of is not very elegant but it should work:
Follow the tutorial here
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2
to transform your Absence Period table in query (duplicate/reference the original query in case you want to keep the original format intact for other visuals or measures).
In the transformed Absence period create a Key column which concatenates Employee and the Date column that results from the transformation.
Create the same column in Timesheets table. Then you should be able to use lookupvalue, or connect the two tables together in the model and get the period directly from your transformed Absence period table.
Let me know if you have any questions.
Proud to be a Super User! | |
Hi @htsvhwave ,
Please try this:
Period2 =
CALCULATE (
MAX ( 'Absence period'[Period] ),
FILTER (
'Absence period',
'Absence period'[Employee] = EARLIER ( Timesheets[Employee] )
&& EARLIER ( Timesheets[Date] ) >= 'Absence period'[Start Date]
&& EARLIER ( Timesheets[Date] ) <= 'Absence period'[End Date]
)
)
Please take note of the following in your sample data:
No result for row 4 as your start date > than your end date
Christ instead of Chris
Proud to be a Super User!
When i try to use your fomula i cant call the column after using the Earlier function
Which table are you entering this formula in? And also, this is supposed to be a calculated column and not a measure. You can't use EARLIER within a measure unless it is used within a virtual table.
Please see attached sample pbix for details:
Proud to be a Super User!
User | Count |
---|---|
100 | |
89 | |
82 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |