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

Highlighted
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

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors