Forum Discussion
Calendar
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
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/409365
See 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/434008
Dear 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:
hi, 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
hi,
Thank you for your answer. I will try to be more specific.
My sample data od the Headcount table is this:
Name Hire Date Status Today Craig 15/04/2019 Permanent 13/05/2019 Vivek 01/03/2019 Fix Term 13/05/2019 Dionysios 01/01/2019 Permanent 13/05/2019 Paulo 01/10/2018 Fix Term 13/05/2019 Ricardo 24/09/2018 Fix Term 13/05/2019 Sueann 01/07/2018 Fix Term 13/05/2019 Cornelis 01/09/2017 Fix Term 13/05/2019 Davey 12/01/2017 Permanent 13/05/2019 Alice 03/10/2016 Permanent 13/05/2019 Stefano 10/02/2015 Permanent 13/05/2019 I have a Date table:
Date = CALENDARAUTO()The Headcount table is linked to the Date table by the Hiredate Many to one.I would like to see how many employees I have per year and then drill by month in a bar chart (sorry I can't paste it here).
At the moment I get the chart showing only new hired employees by month rather than total employees working by month. What am I doing wrong?
Many thanks for your support!
Imy
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