SumIf?

[Anonymous]

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

[amitchandak · Accepted solution]

@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

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

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.

[Anonymous]

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.

[Anonymous]

Thanks for the tip.  I've been so laser focused on building the tables in M/Power Query, I haven't even begun to start looking into DAX.  I'll give this a lookup in the morning...

[mahoneypat]

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