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.
I am having a bit of difficulty trying to get my head around having data on the last six months based on a date field in another table.
Scenario:
I am looking at employees and the number of shifts that they do. I only want to count how many shifts they have completed in the last 6 months of their 'anniversary date'. I have tried using the DATESINPERIOD but it is not customised based on the anniversary date. I have the following tables:
Employee ID | Anniversary Date |
1 | 30 June 2021 |
2 | 23 June 2021 |
3 | 4 September 2021 |
Employee ID | Shift Date |
1 | 29 June 2021 |
1 | 1 January 2021 |
2 | 20 June 2021 |
2 | 31 December 2020 |
3 | 20 May 2021 |
3 | 3 July 2021 |
I have set up the following measure as well:
Number of Shifts = COUNTROWS(Shift Information)
I would like to count the number of shifts that employees have done in the last 6 months from their anniversary date, so this is what I would like the expected output to be:
Employee ID | Number of Shifts |
1 | 1 |
2 | 1 |
3 | 2 |
As you can see any shifts that have fallen out of the six month period (of the employee's anniversary date) are not included.
What would you recommend the next steps be?
Thank you!
Solved! Go to Solution.
Hi, @Anonymous ;
As lbendlin Said , you offer data is all in last 6 months. if you want to according to every Employee ID's Anniversary Date to count . the measure is as follows ( the result is also different with you) :
countrow = CALCULATE(COUNTROWS('Shift'),FILTER('Shift',DATEDIFF([Shift Date],RELATED(Employee[Anniversary Date]),MONTH)<=6))
The final output is shown below:
However, if you want to show the result as yours. you could modify the measure like below:
countrow2 = CALCULATE(COUNTROWS('Shift'),FILTER('Shift',DATEDIFF([Shift Date],MAX(Employee[Anniversary Date]),MONTH)<=6))
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous ;
As lbendlin Said , you offer data is all in last 6 months. if you want to according to every Employee ID's Anniversary Date to count . the measure is as follows ( the result is also different with you) :
countrow = CALCULATE(COUNTROWS('Shift'),FILTER('Shift',DATEDIFF([Shift Date],RELATED(Employee[Anniversary Date]),MONTH)<=6))
The final output is shown below:
However, if you want to show the result as yours. you could modify the measure like below:
countrow2 = CALCULATE(COUNTROWS('Shift'),FILTER('Shift',DATEDIFF([Shift Date],MAX(Employee[Anniversary Date]),MONTH)<=6))
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous maybe it is just me but I think all of the shifts are within the six months up to the anniversary date for each employee?
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |