Calendar

[imy]

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?

[Greg_Deckler]

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-TIT...

[imy]

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:

Count of Active Employee =

VAR currentDate =

MAX ( Headcount[Today] )

RETURN

CALCULATE (

COUNTROWS(Headcount),

FILTER (

Headcount,

( Headcount[Hire Date] <= currentDate

&& Headcount[Today] >= currentDate )

&& Headcount[Active] = 1

)

)

I hope I am explaining my self,

Imy

[v-lili6-msft]

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/38490

And if you could refer to this way to add a new table:

https://community.powerbi.com/t5/Desktop/Sales-forecasting-on-a-per-month-basis-using-projected-tota...

Best Regards,

Lin

[imy]

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

[v-lili6-msft · Accepted solution]

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

[imy]

It worked!! thank ou very much!