cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

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

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
Highlighted
Community Champion
Community Champion

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

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

 

 

Highlighted
Solution Sage
Solution Sage

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

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))))

Highlighted
Helper I
Helper I

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

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?

Highlighted
Helper I
Helper I

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

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

Highlighted
Solution Sage
Solution Sage

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

"

 

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

 

 

 

 

Highlighted
Helper I
Helper I

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

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?

Highlighted
Solution Sage
Solution Sage

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

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?

 

 

 

Highlighted
Helper I
Helper I

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

Code is 

 

BJE_0-1593942053682.png

 

Highlighted
Solution Sage
Solution Sage

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

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

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors