cancel
Showing results for
Did you mean:
Highlighted
Helper IV

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX

Re: SumIf?

@bflury , 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-La...

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

Proud to be a Super User!

4 REPLIES 4
Highlighted
Super User VI

Re: SumIf?

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

Proud to be a Super User!

Highlighted
Helper IV

Re: SumIf?

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

Highlighted
Super User IX

Re: SumIf?

@bflury , 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-La...

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

Proud to be a Super User!

Helper IV

Re: SumIf?

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.

Announcements

Upcoming Events

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

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors
Top Kudoed Authors