Sumif Dates that inside Table

[eisayev]

Hi

My problem is, I have date columns which is start and end dates. In another table I have dates row by row. I merged these two tables and "Table" created in each row depending on Worker. Now I want to sum date ranges but still not successful.

I used such M code but it was not correct.

if [period start] <= Table.Column([NewColumn],"Shift Date") and [Period End] <= Table.Column([NewColumn], "Shift Date") then Table.Column([NewColumn], "Hours Worked") else 0

I could not upload the file because of its size but tried to show problem on picture

Thank you in advance

 

[Bill Szysz]

Try this one

TotalHours = Table.AddColumn(#"Merged Queries2", "Total hours", (x) => List.Sum(Table.SelectRows(x[NewColumn], each ([Shift Dates]>= x[Period Start]) and ([Shift Dates] <= x[Period End]))[Hours worked]) )

 

[eisayev]

Thank you for answer

I am trying to understand the solution you wrote. I started with deviding the formula into parts and my first formula to examine was Table.SelectRows.

Table.SelectRows([NewColumn], each ([Shift Date]>=[Period Start]) and ([Shift Date]<=[Period End]))

When I write this code above it says "The field 'Period Start' of the record wasn't found". I think it is because those columns were not found inside "NewColumn" Table. Somehow I have to show the name of Table I think.

But in your formula there was "x" before "Period" Columns. I tried with "x" and "Column_Data" (the name of table) but still get error.

I am new in M language and trying to figure out how this language works.

Thank you for the effort and time you spent to solve my problem.