Sumif Dates that inside Table

E

eisayev

New member
Joined
Oct 1, 2012
Messages
33
Reaction score
0
Points
0
Location
Baku, Azerbaijan
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
 

Attachments

  • PQ.jpg
    PQ.jpg
    92.2 KB · Views: 22
Try this one
Code: 
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]) )
 
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.

Code: 
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.
 
You must log in or register to reply here.
Back
Top