Forum Discussion
Helper IICalculating a monthly employee count from a start and end date range
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!
Community ChampionOK, 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
Helper II| 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
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
- louisvp
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!