Forum Discussion
SumIf?
Hi folks,
Ending for the night with, I hope, a quick question here...
I have query1 and query2.
Query1 has employee, timesheet_day, num_hours fields which hold the total number of hours per day/employee
Query2 has all employees
I want to add a column to Query2 that essentially sums num_hours from Query1 within the last week. I know this will use DateTime.LocalNow(). However, I'm not sure to the syntax of using a SumIf function.
Any help would be much appreicated!
-Brett
Anonymous , You can have a new column in query 2. Change as per need
New column =
Var _LWS= 'Date'[Date]+-1*WEEKDAY(today(),2)+1 -7 //last week start remove -7 for this week
var _LWE = 'Date'[Date]+ 7-1*WEEKDAY(today(),2) // //last end start remove -7 for this week
return
sumx(filter(query1, query1[Employee] =query2[Employee] && query1[timesheet_day]>=_LWS && query1[timesheet_day]<=_LWE),query1[num_hours])Put you can join your table with employee table and date and can get that data
Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
My Your Tube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy
Appreciate your Kudos.
4 Replies
This is better done with DAX with a relationship on Employee between the tables, and a relationship to a Date table that has a week column in it. You can then use a simple SUM() measure with a visual that has Employee, Week, and your measure.
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Anonymous , You can have a new column in query 2. Change as per need
New column =
Var _LWS= 'Date'[Date]+-1*WEEKDAY(today(),2)+1 -7 //last week start remove -7 for this week
var _LWE = 'Date'[Date]+ 7-1*WEEKDAY(today(),2) // //last end start remove -7 for this week
return
sumx(filter(query1, query1[Employee] =query2[Employee] && query1[timesheet_day]>=_LWS && query1[timesheet_day]<=_LWE),query1[num_hours])Put you can join your table with employee table and date and can get that data
Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
My Your Tube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy
Appreciate your Kudos.Again, meant to say so earlier, but thank you both amitchandak and mahoneypat! Used this tip earlier on. The suggestion of SUMX was exactly what I needed.
