cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
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 IV
Super User IV

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...
My Your Tube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
Highlighted
Community Champion
Community Champion

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

Highlighted
Helper IV
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 IV
Super User IV

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...
My Your Tube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Helper IV
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.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

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

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors