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 ChampionHi 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])))))
Helper IIBut 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