Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have an employee dataset that will be updated daily. The dataset contains a single row for each employee and the columns contain a range of information including title, employment basis, commencement date, termination date, standard hours, etc.
Using this data, I need to calculate totals based on certain characteristics for different intervals (i.e. monthly). The calculation needs to work retrospectively as well as apply to additional data going forward.
I have two data tables for this purpose. An example of the employee data is below:
EMPNO | FTE | COMMDATE | TERMDATE |
1 | 1 | 14-Jul-81 | 2-Jul-04 |
2 | 1 | 3-Nov-16 | |
3 | 1 | 19-Sep-88 | 22-Aug-07 |
4 | 1 | 1-Nov-18 | |
5 | 1 | 1-Nov-18 | |
6 | 1 | 1-Nov-18 | |
7 | 1 | 1-Nov-18 | |
8 | 1 | 1-Nov-18 | 28-Jun-19 |
9 | 1 | 1-Nov-18 | 29-May-19 |
Secondly, I have a dates table as follows:
Year | Month | Month Start Date | Month End Date | Year YTD Start Date | Year YTD End Date |
2015-16 | Jul-15 | 1/07/2015 | 31/07/2015 | 1/07/2015 | 31/07/2015 |
2015-16 | Aug-15 | 1/08/2015 | 31/08/2015 | 1/07/2015 | 31/08/2015 |
2015-16 | Sep-15 | 1/09/2015 | 30/09/2015 | 1/07/2015 | 30/09/2015 |
2015-16 | Oct-15 | 1/10/2015 | 31/10/2015 | 1/07/2015 | 31/10/2015 |
2015-16 | Nov-15 | 1/11/2015 | 30/11/2015 | 1/07/2015 | 30/11/2015 |
2015-16 | Dec-15 | 1/12/2015 | 31/12/2015 | 1/07/2015 | 31/12/2015 |
2015-16 | Jan-16 | 1/01/2016 | 31/01/2016 | 1/07/2015 | 31/01/2016 |
2015-16 | Feb-16 | 1/02/2016 | 29/02/2016 | 1/07/2015 | 29/02/2016 |
2015-16 | Mar-16 | 1/03/2016 | 31/03/2016 | 1/07/2015 | 31/03/2016 |
2015-16 | Apr-16 | 1/04/2016 | 30/04/2016 | 1/07/2015 | 30/04/2016 |
2015-16 | May-16 | 1/05/2016 | 31/05/2016 | 1/07/2015 | 31/05/2016 |
2015-16 | Jun-16 | 1/06/2016 | 30/06/2016 | 1/07/2015 | 30/06/2016 |
I need to create a unique sum of FTE hours for all employees that were current for a given period and do this for all periods. The parameters for a given period would be that COMMDATE <= Month End Date and TERMDATE >= Month Start Date or TERMDATE is null.
I think this needs to be done as an additional column to my date table rather than as a measure but whatever approach I have taken has not worked. Any advice from anyone that has encountered this problem and developed a solution is welcome.
Solved! Go to Solution.
mmhhh ... the problem could be THEN some exchange of table name, hopefully.
let we try to get step clear.
I try to use the name you showed in the screen shot of code you are using. You should control carefully to find any difference.
#"Added Custom" = Table.AddColumn(#"Changed Type", "Monthly ASL", each List.Sum(Table.SelectRows(Empshot, (f)=> f[COMMDATE] <= [Month End Date] and (f[TERMDATE] >= [Month Start Date] or f[TERMDATE] is null))[FTE])),
This means that Empshot is the table aving as columns EMPNO/FTE/COMMDATE/TERMDATE
#"Changed Type" is youtable at previous step.
PS
just as last chance check if there is some blank in one of the names.
You can't really do it as an additional column to the dates table as you would need to add one column per employee.
You can do it as a measure using a CROSSFILTER between the dates table and your employment table with filter direction none (which basically creates a crossjoin on the fly)
Similar to this one
https://community.powerbi.com/t5/DAX-Commands-and-Tips/HeadCount-by-type/m-p/1196060#M19030
Thanks lbendlin. I really need this as a calculated column rather than a measure, as I want to be able to report trend data over multiple periods
or using M on power query:
Table.AddColumn(#"Changed Type", "fte", each List.Sum(Table.SelectRows(tabFTE, (f)=> f[COMMDATE] <= [Month End Date] and (f[TERMDATE] >= [Month Start Date] or f[TERMDATE] is null)[FTE])))
if this calculation is not what you expect, please, give some example on input table and outpu expected table
if FTE is always =1, you could use also this:
Table.AddColumn(#"Changed Type", "fte", each Table.RowCount(Table.SelectRows(tabFTE, (f)=> f[COMMDATE] <= [Month End Date] and (f[TERMDATE] >= [Month Start Date] or f[TERMDATE] is null))))
Thanks. When I entered the expression in it returned an error result with the following message:
Expression.Error: We cannot convert a value of type List to type Table.
Details:
Value=[List]
Type=[Type]
Given the error result, I have recongifured the sample data to better explain the objective of what I am trying to achieve. Call the first table "Source". Below is an extract from the Source table. Note I have added 12 columns for each month with a "Y" indicating that the parameters are met for that period. This is for demonstration purposes only as I do not want to add additional columns to this table as that will involve 12 columns for every year - too many columns.
EMPNO | FTE | COMMDATE | TERMDATE | Jul-19 | Aug-19 | Sep-19 | Oct-19 | Nov-19 | Dec-19 | Jan-20 | Feb-20 | Mar-20 | Apr-20 | May-20 | Jun-20 |
1 | 1 | 14-Jul-81 | 2-Jul-19 | Y | |||||||||||
2 | 0.5 | 3-Nov-16 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | |
3 | 1 | 19-Sep-88 | 2-Jul-20 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
4 | 1 | 1-Nov-18 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | |
5 | 0.75 | 1-Nov-18 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | |
6 | 1 | 1-Nov-18 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | |
7 | 1 | 1-Nov-18 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | |
8 | 1 | 1-Nov-18 | 28-Jun-19 | ||||||||||||
9 | 1 | 1-Nov-18 | 29-May-20 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | |
10 | 0.8 | 1-Nov-18 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | |
11 | 1 | 1-Nov-18 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | |
12 | 1 | 1-Feb-20 | 30-Apr-20 | Y | Y | Y | |||||||||
13 | 1 | 1-Nov-18 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | |
14 | 0.35 | 1-Nov-18 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | |
15 | 1 | 1-Jul-19 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | |
16 | 1 | 1-Nov-18 | 28-Apr-20 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | ||
17 | 0.5 | 1-Nov-18 | 29-Jun-20 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
18 | 1 | 1-Nov-18 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | |
19 | 1 | 1-Nov-18 | 20-Feb-20 | Y | Y | Y | Y | Y | Y | Y | Y | ||||
20 | 1 | 1-Jul-20 |
The "Date" table is where I want my results, in the new column "Total FTE". I have populated the column with the results expected based on the above sample "Source" table but this is the column for which I am trying to find the working expression.
Year | Month | Month Start Date | Month End Date | Year YTD Start Date | Year YTD End Date | Total FTE |
2019-20 | Jul-19 | 1/07/2019 | 31/07/2019 | 1/07/2019 | 31/07/2019 | 14.9 |
2019-20 | Aug-19 | 1/08/2019 | 31/08/2019 | 1/07/2019 | 31/08/2019 | 13.9 |
2019-20 | Sep-19 | 1/09/2019 | 30/09/2019 | 1/07/2019 | 30/09/2019 | 13.9 |
2019-20 | Oct-19 | 1/10/2019 | 31/10/2019 | 1/07/2019 | 31/10/2019 | 13.9 |
2019-20 | Nov-19 | 1/11/2019 | 30/11/2019 | 1/07/2019 | 30/11/2019 | 13.9 |
2019-20 | Dec-19 | 1/12/2019 | 31/12/2019 | 1/07/2019 | 31/12/2019 | 13.9 |
2019-20 | Jan-20 | 1/01/2020 | 31/01/2020 | 1/07/2019 | 31/01/2020 | 13.9 |
2019-20 | Feb-20 | 1/02/2020 | 29/02/2020 | 1/07/2019 | 29/02/2020 | 14.9 |
2019-20 | Mar-20 | 1/03/2020 | 31/03/2020 | 1/07/2019 | 31/03/2020 | 13.9 |
2019-20 | Apr-20 | 1/04/2020 | 30/04/2020 | 1/07/2019 | 30/04/2020 | 13.9 |
2019-20 | May-20 | 1/05/2020 | 31/05/2020 | 1/07/2019 | 31/05/2020 | 11.9 |
2019-20 | Jun-20 | 1/06/2020 | 30/06/2020 | 1/07/2019 | 30/06/2020 | 10.9 |
When I entered the expression suggested I amended it as follows to reflect the data tables I have:
= Table.AddColumn(#"Changed Type", "Total FTE", each List.Sum ( Table.SelectRows ( (Source[FTE]), (f)=> f(Source[COMMDATE]) <= [Month End Date] and f(Source[TERMDATE]) >= [Month Start Date] or f(Source[TERMDATE]) is null )))
Any ideas on why the error message is returned?
"
When I entered the expression suggested I amended it as follows to reflect the data tables I have:
= Table.AddColumn(#"Changed Type", "Total FTE", each List.Sum ( Table.SelectRows ( (Source[FTE]), (f)=> f(Source[COMMDATE]) <= [Month End Date] and f(Source[TERMDATE]) >= [Month Start Date] or f(Source[TERMDATE]) is null )))
Any ideas on why the error message is returned?
Expression.Error: We cannot convert a value of type List to type Table.
Details:
Value=[List]
Type=[Type]
"
Yes I think I have.
Seems you made a sort of mix of the two solution I proposed.
The first one use the list.sum function which expect a lis as argument.
The second one use the table.rowcount wich use a table.
Let we use only the first one.
The function Table.SelectRows expect as first argument a table non a list as you provided.
Table.AddColumn(#"Changed Type", "fte", each List.Sum(Table.SelectRows(Source, (f)=> f[COMMDATE] <= [Month End Date] and (f[TERMDATE] >= [Month Start Date] or f[TERMDATE] is null)[FTE])))
You should pay attenzion to the red part. That slice the column FTE from the selected rows and then sum over that the values list.
PS
I will reflect on the new data and explanation you give ...
Hi
I see what I did wrong in converting the expression provided.
However, using the updated expression I still get the error message as follows:
Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=[Table]
Type=[Type]
Thoughts? Do you get the same result if you create a report with these sample datasets?
no. I used your newer data and get the similar(not the exact numbers) result you exposed.
the code of calcFTE query is this:
source = input{[Item="tabPeriod",Kind="Table"]}[Data],
#"Added Custom" = Table.AddColumn(source, "fte", each List.Sum(Table.SelectRows(emploiesData, (f)=> f[COMMDATE] <= [Month End Date] and f[TERMDATE] >= [Month Start Date] or f[TERMDATE] is null)[FTE])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Year YTD Start Date", "Year YTD End Date"})
in
#"Removed Columns"
Could you show the queries and codes you use?
Code is
ok. probably my wrong indicataion when corrected your previous error:
################wrong################
Table.AddColumn(#"Changed Type", "fte", each List.Sum(Table.SelectRows(Source, (f)=> f[COMMDATE] <= [Month End Date] and (f[TERMDATE] >= [Month Start Date] or f[TERMDATE] is null)[FTE])))
################correct################
Table.AddColumn(#"Changed Type", "fte", each List.Sum(Table.SelectRows(Source, (f)=> f[COMMDATE] <= [Month End Date] and (f[TERMDATE] >= [Month Start Date] or f[TERMDATE] is null))[FTE]))
difference is in position of [FTE] which must stay after "))" and before "))" not between ")" and ")))"
in this way also the numbers are exactly what you expectd
Thanks
We I make that change to the expression, I get a new error:
Expression.Error: The column 'FTE' of the table wasn't found.
Details:
FTE
??
i ussed this scheme for the table of employ data:
The expression used
Table.AddColumn(source, "fte", each List.Sum(Table.SelectRows(emploiesData, (f)=> f[COMMDATE] <= [Month End Date] and (f[TERMDATE] >= [Month Start Date] or f[TERMDATE] is null))[FTE]))
refers to these labels.
If your column names are different, change the formula or the anmes of columns consequently.
I have the same column labels but still return an error message, not recognising the "FTE"?
mmhhh ... the problem could be THEN some exchange of table name, hopefully.
let we try to get step clear.
I try to use the name you showed in the screen shot of code you are using. You should control carefully to find any difference.
#"Added Custom" = Table.AddColumn(#"Changed Type", "Monthly ASL", each List.Sum(Table.SelectRows(Empshot, (f)=> f[COMMDATE] <= [Month End Date] and (f[TERMDATE] >= [Month Start Date] or f[TERMDATE] is null))[FTE])),
This means that Empshot is the table aving as columns EMPNO/FTE/COMMDATE/TERMDATE
#"Changed Type" is youtable at previous step.
PS
just as last chance check if there is some blank in one of the names.
Thank you. Double checking my parameters I have now been able to record the results I was after. Thanks for your assistance.