Forum Discussion
Calendar
How can I use the in-built calendar in Power BI to for example calculate the number of employees working in the company per year and month?
hi, imy
The Headcount table is linked to the Date table by the Hiredate Many to one.
You couldn't link it to date table.
here are two ways for you refer to:
1. Use this formula to add a measure directly.
Count of Active Employee = VAR currentDate = MAX ( 'Date'[Date] ) RETURN CALCULATE ( COUNTROWS ( Headcount ), FILTER ( Headcount, ( Headcount[Hire Date] <= currentDate && Headcount[Today] >= currentDate ) ) )Then drag date column from date table and this measure into a bar chart
2. Use this formula create a new table
Table = FILTER(CROSSJOIN(Headcount,'Date'),'Date'[Date]>=Headcount[Hire Date]&&'Date'[Date]<=Headcount[Today])
Then create a measure by this formula
countHeader = CALCULATE(DISTINCTCOUNT('Table'[Name]))Now drag Date field from this new table and the measure into a bar chart.
Result:
and here is sample pbix file, please try it,
Best Regards,
Lin
6 Replies
You can create a Calendar table using CALENDAR or CALENDARAUTO but not sure about a built-in calender. This is really dependent on your data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.
Here are some other things that might help, hard to say without more information.
That being said, take a look at these two Quick Measures as I think you want something like them.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008Dear Greg,
Thank you for your reply. I have managed to add a calendar table and use the following formula that I saw in another answer.
What I am trying to do is something similar to your Periodic Billing. I have a list of employees and their starting date. So I want to see how many employees we have per month and per year.
However, using the formula below brings me the total new employees per month today, rather than the total employees working in the company per month, what am I doing wrong? By the way, I must mention that I dont have an end date for each employee as they are all active. I have added a column for Today's date to use as 'End Date'. So the [End Date] = Today() in my table and the 'Date' [Date] = Today() in my table.
Count of Active Employee = VAR currentDate = MAX ( 'Date'[Date] ) RETURN CALCULATE ( COUNTROWS ( Table1 ), FILTER ( Table1, ( Table1[Start Date] <= currentDate && Table1[End Date] >= currentDate ) && Table1[Active?] = 1 ) )My formula looks like this:
Count of Active Employee =VAR currentDate =MAX ( Headcount[Today] )RETURNCALCULATE (COUNTROWS(Headcount),FILTER (Headcount,( Headcount[Hire Date] <= currentDate&& Headcount[Today] >= currentDate )&& Headcount[Active] = 1))I hope I am explaining my self,Imyhi, imy
Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490And if you could refer to this way to add a new table:
Best Regards,
Lin
