Forum Discussion

imy's avatar
imy
Helper I
7 years ago
Solved

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?
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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's avatar
imy
Helper I
7 years ago

hi,

 

Thank you for your answer. I will try to be more specific.

My sample data od the Headcount table is this:

NameHire DateStatusToday
Craig15/04/2019Permanent13/05/2019
Vivek01/03/2019Fix Term13/05/2019
Dionysios01/01/2019Permanent13/05/2019
Paulo01/10/2018Fix Term13/05/2019
Ricardo24/09/2018Fix Term13/05/2019
Sueann01/07/2018Fix Term13/05/2019
Cornelis01/09/2017Fix Term13/05/2019
Davey12/01/2017Permanent13/05/2019
Alice03/10/2016Permanent13/05/2019
Stefano10/02/2015Permanent13/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's avatar
v-lili6-msft
Community Support
7 years ago

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's avatar
    imy
    Helper I
    7 years ago

    It worked!! thank ou very much!