Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BJE
Helper I
Helper I

Creating sum function in date table from conditional data in another table

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:

 

EMPNOFTECOMMDATETERMDATE
1114-Jul-812-Jul-04
213-Nov-16 
3119-Sep-8822-Aug-07
411-Nov-18 
511-Nov-18 
611-Nov-18 
711-Nov-18 
811-Nov-1828-Jun-19
911-Nov-1829-May-19

 

Secondly, I have a dates table as follows:

 

YearMonthMonth Start DateMonth End DateYear YTD Start DateYear YTD End Date
2015-16Jul-151/07/201531/07/20151/07/201531/07/2015
2015-16Aug-151/08/201531/08/20151/07/201531/08/2015
2015-16Sep-151/09/201530/09/20151/07/201530/09/2015
2015-16Oct-151/10/201531/10/20151/07/201531/10/2015
2015-16Nov-151/11/201530/11/20151/07/201530/11/2015
2015-16Dec-151/12/201531/12/20151/07/201531/12/2015
2015-16Jan-161/01/201631/01/20161/07/201531/01/2016
2015-16Feb-161/02/201629/02/20161/07/201529/02/2016
2015-16Mar-161/03/201631/03/20161/07/201531/03/2016
2015-16Apr-161/04/201630/04/20161/07/201530/04/2016
2015-16May-161/05/201631/05/20161/07/201531/05/2016
2015-16Jun-161/06/201630/06/20161/07/201530/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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

 

image.png

 

#"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.

View solution in original post

14 REPLIES 14
lbendlin
Super User
Super User

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

Anonymous
Not applicable

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.

 

EMPNOFTECOMMDATETERMDATEJul-19Aug-19Sep-19Oct-19Nov-19Dec-19Jan-20Feb-20Mar-20Apr-20May-20Jun-20
1114-Jul-812-Jul-19Y           
20.53-Nov-16 YYYYYYYYYYYY
3119-Sep-882-Jul-20YYYYYYYYYYYY
411-Nov-18 YYYYYYYYYYYY
50.751-Nov-18 YYYYYYYYYYYY
611-Nov-18 YYYYYYYYYYYY
711-Nov-18 YYYYYYYYYYYY
811-Nov-1828-Jun-19            
911-Nov-1829-May-20YYYYYYYYYYY 
100.81-Nov-18 YYYYYYYYYYYY
1111-Nov-18 YYYYYYYYYYYY
1211-Feb-2030-Apr-20       YYY  
1311-Nov-18 YYYYYYYYYYYY
140.351-Nov-18 YYYYYYYYYYYY
1511-Jul-19 YYYYYYYYYYYY
1611-Nov-1828-Apr-20YYYYYYYYYY  
170.51-Nov-1829-Jun-20YYYYYYYYYYYY
1811-Nov-18 YYYYYYYYYYYY
1911-Nov-1820-Feb-20YYYYYYYY    
2011-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.

 

YearMonthMonth Start DateMonth End DateYear YTD Start DateYear YTD End DateTotal FTE
2019-20Jul-191/07/201931/07/20191/07/201931/07/201914.9
2019-20Aug-191/08/201931/08/20191/07/201931/08/201913.9
2019-20Sep-191/09/201930/09/20191/07/201930/09/201913.9
2019-20Oct-191/10/201931/10/20191/07/201931/10/201913.9
2019-20Nov-191/11/201930/11/20191/07/201930/11/201913.9
2019-20Dec-191/12/201931/12/20191/07/201931/12/201913.9
2019-20Jan-201/01/202031/01/20201/07/201931/01/202013.9
2019-20Feb-201/02/202029/02/20201/07/201929/02/202014.9
2019-20Mar-201/03/202031/03/20201/07/201931/03/202013.9
2019-20Apr-201/04/202030/04/20201/07/201930/04/202013.9
2019-20May-201/05/202031/05/20201/07/201931/05/202011.9
2019-20Jun-201/06/202030/06/20201/07/201930/06/202010.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?

Anonymous
Not applicable

"

 

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?

Anonymous
Not applicable

no. I used your newer data and get the similar(not the exact numbers)  result you exposed.

 

 

 

image.png

 

 

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 

 

BJE_0-1593942053682.png

 

Anonymous
Not applicable

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

 

??

Anonymous
Not applicable

i ussed this scheme for the table of employ data:

 

image.png

 

 

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"?

 

BJE_0-1593948114841.png

 

Anonymous
Not applicable

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.

 

image.png

 

#"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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors