Count of Rows using Master Calendar

[rinaemo]

Hi Experts,

I was told to create a master calendar to count the number of employees for the start and end date by Month and Year. I am very new to PowerBI and i have no idea how to start with and how to proceed with the data.

Over here i have 4 columns in my current table:

1. Employee ID

2. Employee Name

3. Join Date

4. End Date

I need to create a visualization of Month and Year of how many employees is present in the company...

Can anyone advise on how do i create this? Much appreciated! Thanks!

[v-yueyunzh-msft]

Hi , @rinaemo 

According to your description, you want to "create a visualization of Month and Year of how many employees is present in the company.". Right?

Here are the steps you can refer to :
(1)This is my test data:

(2)We can create a master date you need , we can click "New Table" and enter this:

Calendar = ADDCOLUMNS( 
CALENDAR(FIRSTDATE('Table'[Join Date]),TODAY()),
"year", YEAR ( [Date] ),
"month", MONTH([Date])
)

And we do not need to make relationship between two tables.

(3)In the fact table , we need to click "New Column" to create a calculated column to show the end [End Date] if the [End Date] is BLANK() then we retun the TODAY() date:

End Date result = IF([End Date] = BLANK() , TODAY() , [End Date])

(4)Then we can create a measure like this:

Measure = var _cur_year_month = MAX('Calendar'[year_month])
var _t = FILTER( 'Table' , _cur_year_month >= YEAR('Table'[Join Date])*100+MONTH('Table'[Join Date])  && _cur_year_month<=YEAR('Table'[End Date result])*100+MONTH('Table'[End Date result]))
return
COUNTROWS(_t)

(5)Then we put the fields we need on the visual and we can meet your need:

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

[rinaemo]

Hi Aniya,

My end date is from the initial table, how do i do the following to link to my other table? 

End Date result = IF([End Date] = BLANK() , TODAY() , [End Date])

On top of that, the requirement just changed to only calculating dates that are of 1st of the month and year. I need to take in consideration of the start date and end date on every 1st of the month to keep track of the number of employees in the company. 

[v-yueyunzh-msft]

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

[rinaemo]

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.

[v-yueyunzh-msft · Accepted solution]

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

[rinaemo]

Ohh ya, fyi the data for my PowerBI is pulled directly from a live SQL database.

[ryan_mayu]

@rinaemo 

pls see the attachment below. hope this is helpful