How to get data according to date conditions

[Linhle]

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

[Jihwan_Kim]

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

[Linhle]

@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!

[Jihwan_Kim]

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

[Linhle]

@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!

[Jihwan_Kim · Accepted 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 

[Linhle]

@Jihwan_Kim  That's exactly what I needed. Thanks for your great support!

[Linhle]

@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!

[Jihwan_Kim]

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

[Linhle]

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!

[Jihwan_Kim]

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.

[Linhle]

I couldn’t have done it without you!

[amitchandak]

@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))

[Linhle]

@amitchandak Thanks for your reply!

When I use your mearsure, something went wrong. Can you check it?