Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
robhel
Helper I
Helper I

Sum of hours against multiple projects, resources and years

Hello out there Power BI Gurus,

 

I am after some HELP with creating a calculation between 2 queries within Power BI.......I have extracted separate time recording data for each FY and need to sum the hours against unique projects........

 

Time Recording Data:

 

Project    Hours        Date              Employee

 

50001       3.5       20/01/2017      Donald Duck

50002       4.5       20/01/2017      Donald Duck

50001       2.5       20/01/2017      James Dean

50004       3.2       20/01/2017      James Dean

50001       4.3       21/01/2017      Nicole Kidman

50002       2.5       21/01/2017      Donald Duck

50003       7.5       22/01/2017      Tony Blair

50001       4.5       22/01/2017       James Dean

50002       7.5       22/01/2017       Nicole Kidman

50004       7.5       23/01/2017       Nicole Kidman

50004       7.5       23/01/2017       Tony Blair

50004       7.5       24/01/2017        Princess Kate

50002       3.5       24/01/2017        Donald Duck

 

Power BI Query:

 

Project    FY15Hours   FY16Hours   FY17Hours    FY18Hours

50001      125.5            275.35         678.25           325.5

50002      1725.5          875.75         374.00           0

50003      0                   0                1285.00           2789.25

50004      0                   72.35          1589.00           0

 

As my time recording reports have been extracted separately based on each FY, I can query each report, however I am lost at how to easily do the sum of hours against multiple entries

 

Greatly appreciate assistance, including actual script of how to do this.

..

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

 

You may download my solution from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

 

You may download my solution from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Seward12533
Solution Sage
Solution Sage

On my phone but the best approach is to have a single table with all years. Formatted like your first for all years linked to a date table. Then you just need to build a matrix visual with project on rows and Year from the date table on the columns and a simple measure like Total_Hrs = sum(hours). PowerBI will then do it’s magic.

If you really only have the second table with the pivoted hours by project and year then it would be best to unpibot in PowerQuery before it gets loaded into your model. Once you unpivot you will need to convert the FY16 tc into dates.

If you want to just go with what you have you will need a bridge table of just project numbers (I would not recommend using the new many-many relationship. And then write one measure like above to sum current year and the write a measure to sum the hours for each year column.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.