Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello everyone,
Currently I'm struggling a bit and I hope someone can help me with the following.
I've got 2 tables like this:
Table 1: Onboarding /Offboarding Info
Emp Code | Full name | Onboard Date | Offboard Date |
BA1570 | Mr.119 | 8/8/2020 | |
BA1458 | Mr.225 | 5/9/2020 | |
BA1655 | Mr.033 | 1/29/2021 | 3/24/2021 |
BA1580 | Mr.109 | 8/17/2020 |
Table 2: Changing office Info
Emp Code | Final Office | Date of change |
BA1570 | HOME-A12 | 11/1/2020 |
BA1570 | HOME-A11 | 1/1/2021 |
BA1570 | HOME-A12 | 2/1/2021 |
BA1458 | HOME-A12 | 9/1/2020 |
BA1458 | HOME-A1 | 1/1/2021 |
BA1458 | HOME-A12 | 2/1/2021 |
BA1655 | HOME-A11 | 1/29/2021 |
BA1580 | HOME-A12 | 8/17/2020 |
I need to produce visualizations that have 2 slicers (Date and Office). When I filter these slicers, I have information about all employees are working according to the filtered office, filtered date and their seniority.
The employee's seniority is counted from 'Onboard Date' to max of filtered date if the max of filtered date is less than 'Offboard Date'. The employee's seniority is counted from 'Onboard Date' to 'Offboard Date' if the max of filtered date is greater than 'Offboard Date'.
=> What I am expecting are results like:
Example 1: When I choose Slicer 'Date' from 11/1/2020 to 11/30/2020 and Slicer 'Office' is HOME-A12. The result is as below:
Emp Code | Full name | Onboard Date | Offboard Date | Seniority (month) |
BA1570 | Mr.119 | 8/8/2020 | 3 | |
BA1458 | Mr.225 | 5/9/2020 | 6 | |
BA1580 | Mr.109 | 8/17/2020 | 3 |
Example 2:
When I choose Slicer 'Date' from 11/30/2020 to 1/30/2021 and Slicer 'Office' is HOME-A11. The result is as below:
Emp Code | Full name | Onboard Date | Offboard Date | Seniority (month) |
BA1570 | Mr.119 | 8/8/2020 | 5 | |
BA1655 | Mr.033 | 1/29/2021 | 0 |
When I choose Slicer 'Date' from 11/30/2020 to 1/30/2021 and Slicer 'Office' is HOME-A12. The result is as below:
Emp Code | Full name | Onboard Date | Offboard Date | Seniority (month) |
BA1580 | Mr.109 | 8/17/2020 | 5 |
Example 3:
When I choose Slicer 'Date' from 1/30/2021 to 4/30/2021 and Slicer 'Office' is HOME-A11. The result is as below:
Emp Code | Full name | Onboard Date | Offboard Date | Seniority (month) |
BA1655 | Mr.033 | 1/29/2021 | 3/24/2021 | 2 |
When I choose Slicer 'Date' from 1/30/2021 to 4/30/2021 and Slicer 'Office' is HOME-A12. The result is as below:
Emp Code | Full name | Onboard Date | Offboard Date | Seniority (month) |
BA1570 | Mr.119 | 8/8/2020 | 8 | |
BA1458 | Mr.225 | 5/9/2020 | 11 | |
BA1580 | Mr.109 | 8/17/2020 | 8 |
Thanks,
Linh
P/s: Here is full data: https://drive.google.com/drive/u/0/folders/1nG0x4FgStCatCtqVZBP7Vh7KGYQIS5A0
Solved! Go to Solution.
Hi, @Linhle
Please check the below link. (please ignore the last link that I uploaded a few minutes ago.)
https://www.dropbox.com/s/hovaky7bcwbifel/linhle.pbix?dl=0
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @Linhle
Please check the below picture and the sample pbix file's link down below.
All measures are in the sample pbix file.
https://www.dropbox.com/s/hovaky7bcwbifel/linhle.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Jihwan_Kim Thanks for replying. It is almost like my desired result.
However, I need my visualization that shows all employees are working at the selected office and the selected time.
For instance, in your above picture, Emp Code - BA 1570 onboarded in August 2020 so he still remains on the visualization in September, October, November and December 2020 at HOME-A12. In January 2021, BA 1570 changed to HOME-A11 so he appeared on the visualization in January 2021 at HOME-A11 and did not appeared from the visualization in January 2021 at HOME-A12.
Please help me to solve this problem!
Hi, @Linhle
Thank you for your feedback.
Please check the link down below.
https://www.dropbox.com/s/hovaky7bcwbifel/linhle.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Jihwan_Kim About the first problem, It still remains an invalid case. Employees that have 'Offboard Date' in this month will be disappeared from visualization in next month.
For example, we can see these pictures below. BA 1406 and BA 1412 offboarded in February 2020 so they should disappear by March 2020 and the months thereafter.
You don't mind if you check it again.
Sorry for any inconvenience caused!
Hi, @Linhle
Please check the below link. (please ignore the last link that I uploaded a few minutes ago.)
https://www.dropbox.com/s/hovaky7bcwbifel/linhle.pbix?dl=0
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Jihwan_KimYour answer to this problem is purely my wish. But I regret to say that I missed another problem about seniority measure.
The employee's seniority is counted from 'Onboard Date' to max of filtered date if the max of filtered date is less than 'Offboard Date'. On the other hand, the employee's seniority is counted from 'Onboard Date' to 'Offboard Date' if the max of filtered date is greater than 'Offboard Date'.
For example, we can see the picture below. I choose 'Now 2020' so the seniority of BA1618 should be 0 month, not 4 months.
Please check again.
Sorry for any inconvenience!
Hi, @Linhle
Thank you for your feedback.
I do not see the same screen when I selected the same in slicers as your screenshot.
However, I did realize that I made a mistake. So, I amended two measures.
Emp Code Measure and Date Seniority measure.
Please kindly check.
And sorry to say that it is quite difficult to correctly understand the sample model. There might be an incorrect situation again later, that I did not realize after I amend two measures. If you find it, please let me know.
https://www.dropbox.com/s/hovaky7bcwbifel/linhle.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @Jihwan_Kim,
I wanna produce another visualization that show some properties according to all customers. I try to create this visualization by your measures but it doesn't work.
Would you mind helping me again?
This is my desired result:
- Average seniority is the average number of seniority employees in the store during the selected date.
- Seniority less than 3 months is the number of employees with less than 3 months of seniority during the selected date. (same with the rest of the properties)
- %Seniority less than 3 months is the ratio of employees with less than 3 months of seniority to the total number of employees in the store during the selected date. (same with the rest of the properties).
Thanks very much!
Hi, @Linhle
I am not quite sure if I understood your question correctly, but please check the link down below.
https://www.dropbox.com/s/mcp1us7pgfzkwnz/linhle%20%281%29.pbix?dl=0
All measures are in the sample pbix file.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
I couldn’t have done it without you!
@Linhle , Assuming Table1 and Tabl2 are joined on emp code
var _max1 = maxx(allselected('Date'), Date[Date])
var _min1 = minx(allselected('Date'), Date[Date])
return
sumx(values(Table2[Emp Code]) , datediff(if(_min1 >= min( Table1[Onboard Date]), min( Table1[Onboard Date]) , _min1), if(_max < min(Table1[Offboard Date]) || isblank( Table1[Offboard Date]) , min( Table1[Offboard Date], _max1)),month))
@amitchandak Thanks for your reply!
When I use your mearsure, something went wrong. Can you check it?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
105 | |
78 | |
35 | |
35 |
User | Count |
---|---|
157 | |
103 | |
71 | |
65 | |
53 |