Calculating a monthly employee count from a start and end date range

[louisvp]

Dear Community,

In Power BI Desktop I created this table 

Employee count =
VAR ExpandedTable =
GENERATE(
CALENDAR(DATE(2015;01;01);TODAY());
FILTER(
'CREmployee';
[Date]>='CREmployee'[Start date] &&
[Date]< IF(ISBLANK('CREmployee'[Start date]);TODAY();'CREmployee'[End date])
)
)

RETURN
SUMMARIZE(
ExpandedTable;
[Date] ; [Employee name];[Employee Status]; [Created Year]; [Start date];[ End date]; "Count";COUNTROWS('CRJob')
)

After I did this the Power BI Desktop has become very slow.

What I want to do now is to create this table in Dataflows in Power BI Service/online.

To do this I need to use M Query instead of DAX code.

Could one of you please help me how I can create the cummulative table in M Query (Power Query)?

Thanks you very much

Kind regards Louis van Paassen

[dax]

Hi louisvp, 

It seems that you want to create a table by M code, I suggest that you could use filter in column to select the date range , then use GroupBy to create group and create aggregation. If possible, could you please inform me more detailed information (such as your sample data and your expected output)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[louisvp]

Dear Zoe Zhi and AZ 38,

I want to create a table in Power Query with these columns:

[Date]; [Employee name]; [Employee Status]; [Created Year]; [Start datum]; [Einddatum]; "Aantal"; COUNTROWS ('CREmployee')

This table must give me the number of employees with are in my company on every date starting from the first of January 2015.

The first row in this table contains the first of January 2015 in the date column.

The second row contains the second of Janaury 2015

The last row in this table is today

Therefor in DAX I use this code CALENDAR (DATE (2015; 01; 01); VANDAAG ());

I do already have a table called CREmployee which contains the contract start- and enddate of the contract of the employee.

You suggest to make a Join. What do you exactly mean by that? Do I first need to create a datetable starting the first of Janaury 2015 till today?

I used this DAX code so a table is created which calculates the number of employee. When an enddate is filled in in the table the employee is not counted anymore because he doesn't work for us anymore

VAR ExpandedTable =
GENERATE (
CALENDAR (DATE (2015; 01; 01); VANDAAG ());
FILTER (
'CREmployee';
[Date]> = 'CREmployee' [Startdatum] &&
[Date] <IF (ISBLANK ('CREmployee' [Start date]); TODAY (); 'CREmployee' [End date])
)
)

I hope this makes my question better to understood.

THanks for your both help.

Kind regards

Louis

[louisvp]

Additional screen shots

1 The DAX code plus the table which is created

2 The Graph with the cummulative count of employees  per date

[az38]

Hi @louisvp 

If you want to get the number of employees on each day in the period why do you need fields  "[Employee name]; [Employee Status]; " in your table?

if you create a date table

CALENDAR (DATE (2015; 01; 01); today ())

its going to be enough to create a new measure in this date table like this

number of employees = calculate(DISTINCTCOUNT(CREmployee[Employee name]); filter(All(CREmployee); AND(CREmployee[Start Date]<=SELECTEDVALUE(DateTable[Date]); or(isblank(CREmployee[End Date]); CREmployee[End Date]>=SELECTEDVALUE(DateTable[Date])))))

[louisvp]

Thanks.

But how do you create this in de M Query?

I already know how to do this in DAX. But I am not able to do this in M Query / Power Query

Thanks agian
Louis

[az38]

OK, @louisvp 

It will not be easy, but I try to describe

1. you need to create a date table. For this you can use this link 

repeat all steps before "Now add an Index column". It should be enough

Let's say it will create a table called Calendar with the onli field CalendarDate

2. Add custom column to your table Calendar with custom formula

= let mydate=[CalendarDate] in Table.SelectRows(CREmployee, each [Start Date]<=mydate and [End Date]>=mydate)

It will add a column-table to your calendar date

3.  Expand your new column and choose only field 

[Employee name]

You will get a table with 2 columns: CalendarDate and  Employee name

4. Select column CalendarDate, right click and Group By function.

Choose Count Distinct Rows AS Operation

5. Enjoy

totally, you will have a table  Calendar

let
    Source = Query(#date(2015, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2015,1,1)), #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"CalendarDate", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let mydate=[CalendarDate] in Table.SelectRows(CREmployee, each [Start Date]<=mydate and [End Date]>=mydate)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Employee name"}, {"Custom.Employee name"}),
    #"Grouped Rows" = Table.Group(#"Expanded Custom", {"CalendarDate"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}})
in
    #"Grouped Rows"

One moment. if you will have a date with 0 employees it will give you value 1 as count distinct rows

Thx this topic for a second part of solution  https://community.powerbi.com/t5/Desktop/Power-Query-only-Join-on-Range-of-Dates/td-p/505197 

do not hesitate to kudo posts and mark solutions as solution

[louisvp]

Employee name	Start Date	End Date
Noranchelo  Bonafasia	2-7-2018	31-12-2019
Luuk de Niet	2-1-2019	1-1-2020
Nina  Ching-Yong	3-9-2018	2-9-2019
José  Oosterlee	3-6-2019
George  Vischer	1-4-2019
Aryan  Fatehifar	21-1-2019
Stephan van der Linden	16-8-2017	15-2-2020
Wouter  Steffens	3-9-2018	2-9-2019
Huub  Rulkens	15-2-2019
Justin-Bryan  Gross	1-10-2018	30-9-2019
Sven  Ritstier	3-12-2018	2-12-2019
Ricardo  Rohde	1-2-2019
Orhan  Turksever	17-9-2018	16-9-2019
Bram van Stekelenburg	3-6-2019
Christian  Reijers	29-4-2019
Malcolm  Manley	4-2-2019	3-2-2020

Thanks,

Above you will find my table EmployeeList

When I do this step:

#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Employee name"}, {"Custom.Employee name"}),

The Employee Name is not available

Any idea what goes wrong here?

Thanks Louis

[az38]

Hi @louisvp 

it looks like you need either rename your column1 to CalendarDate or (better) replace CalendarDate to Column1 in my source code

    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let mydate=[Column1] in Table.SelectRows(CREmployee, each [Start Date]<=mydate and or([End Date]>=mydate;isblank([End Date])))),

notice, i forgot before to check if isblank([End Date)

next, be sure that type of columns Start date and end date is Date type

do not hesitate to kudo posts and mark solutions as solution

[louisvp]

Thanks AZ 38,

When I use this code

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let mydate=[Column1] in Table.SelectRows(CREmployee, each [Start Date]<=mydate and or([End Date]>=mydate;isblank([End Date]))))

I got an error "Token Literal expected" and a red snake line under the word "or"

Could you help me with this?

We will finaly get there!

You help is very much appreciated!

[az38]

@louisvp 

my bad, sorry

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let mydate=[Column1] in Table.SelectRows(CREmployee, each [Start Date]<=mydate and ([End Date]>=mydate OR [End Date]=null )))

do not hesitate to kudo useful posts and mark solutions as solution

[louisvp]

Hi AZ38.

To test I created a new table with no empty cells in the column "End Date".

This worked with this code:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let mydate=[Column1] in Table.SelectRows(CREmployee2, each [StartDate]<=mydate and ([EndDate]>=mydate))),

This works when a employee has got no End Date:

= Table.AddColumn(#"Changed Type", "Custom", each let mydate=[Column1] in Table.SelectRows(CREmployee2, each [StartDate]<=mydate and ([EndDate]=null)))

But I still got a red snake line under the "OR" when I use this code:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let mydate=[Column1] in Table.SelectRows(CREmployee2, each [Start Date]<=mydate and ([End Date]>=mydate OR [End Date]=null )))

I Hope you are able to help me with the OR Error.

Thanks Louis

[az38]

@louisvp 

in correct examples you are using EndDate without space

in incorrect example you are using [End Date] with space. Please,check

the same is about [Start date]

[louisvp · Accepted solution]

This Works!

let
Source = Query1(#date(2003, 2, 28), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2002,02,28)), #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let mydate=[Column1] in Table.SelectRows(CREmployeeList, each [StartDate]<=mydate and ([EndDate]>=mydate or [EndDate]=null))),
#"Custom uitgevouwen" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Employee name"}, {"Custom.Employee name"}),
#"Grouped Rows" = Table.Group(#"Custom uitgevouwen", {"Column1"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}})
in
#"Grouped Rows"

Thanks AZ68!

[az38]

@louisvp 

check first row

Source = Query1(#date(2003, 2, 28), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2002,02,28)), #duration(1, 0, 0, 0)),

different dates (2003 and 2002)

Ur welcome!

do not hesitate to kudo useful posts and mark solutions as solution

[az38]

Hi @louisvp 

I dont see your data model, but good practie to make cross join (it works similar as GENERATE)  operation in Power Query mode is to add Table as Custom Column to the Table1

For that go to Table1, press Custom Column in Add Column ribbon and in the field Custom Column formula type just

= Table2

then expand the fields from Table2.

but of course,  first you need to prepare your data in both tables with filters