Forum Discussion
Count of Rows using Master Calendar
Hi , rinaemo
Thanks for yoour quick response !
According to your description, you just want to calculate the number of people who are on the job in the middle of the year and month, and for your needs, I have implemented it in the above example, and it is according to your logic to calculate.
If you want to filter based on the year and month you choose, you just need to put [Date] in 'Calendar' in slicer, and then you can implement your needs,like this:
If this method does not meet your needs, you can provide us with the desired output sample screenshot, so that we can better help you solve the problem.
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi, rinaemo
Hello, if your end date is from another table, then you can establish a relationship for the two tables, and then get the corresponding end date.
For your following needs, if you can, I hope you can provide us with sample data of all your tables, as well as your relationship, and give us the final result you want in the form of a table according to the sample data, so that we can better help you.
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Aniya,
My raw data looks like this:
| Employee No: | Name: | Employment Type: | Join Date | End Date |
| 1235250 | Albert | Contractor | 1 Jan 2022 | 9 Dec 2022 |
| 3930434 | Jenny | Perm | 5 May 2010 | |
| 2532403 | Marlina | Perm | 10 May 2022 | |
| 2402402 | Charlotte | Contract | 1 Mar 2000 | 5 Nov 2010 |
| 2402440 | Tristan | Perm | 3 May 2005 | 3 May 2022 |
I am looking to see a chart visualization with card filter to allow me to select the Month and Year with the data showing the number of employees in the company as of 1st of every month. So lets say for May 2022, given that Jenny, Marlina and Tristan are still working in the company, i should get the count of 3 Employees working in the company, with all 3 of them being the Perm staff.
My superior told me to use a master calendar to manage the dates as this data will be for long-run to capture the monthly data of the number of employees all the way from 2000 and go on from there.
Ohh ya, fyi the data for my PowerBI is pulled directly from a live SQL database.
Hi , rinaemo
Thanks for yoour quick response !
According to your description, you just want to calculate the number of people who are on the job in the middle of the year and month, and for your needs, I have implemented it in the above example, and it is according to your logic to calculate.
If you want to filter based on the year and month you choose, you just need to put [Date] in 'Calendar' in slicer, and then you can implement your needs,like this:
If this method does not meet your needs, you can provide us with the desired output sample screenshot, so that we can better help you solve the problem.
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly