Forum Discussion
Summarize data between dates
My starting data is an excel file with the following columns:
| Sheet Name | Assigned To | Start Date | End Date | Scheduled Hours | Duration | Overtime | Prorated Hours |
| ABC Co | Jane Doe | 07/18/16 | 08/12/16 | 160 | 20d | 80 |
Prorated Hours is a calculated cell that returns the amount of hours from the start date through the end of that month (7/31/16 in this example) based on the number of working days between Start Date and the end of the month, and the number of hours per day, which is derived from Scheduled Hours, Overtime (which is true or false) and the number of working days between Start Date and End Date.
My ideal goal would be to use the timeline slicer in a visualization that filters the result of prorated hours in a measured column. However, just a measured column that returns the amount of hours from a given start date field to the end of a month end is fine, assuming it is within the month I am interested in.
The genesis of this need is to show forecasted hours visualizations from a table of project assignments by employee; basically a backlog database.
Any assistance or references to other help articles is greatly appreciated!
I guess there are many approaches. If it were me, I would put some effort into shaping the data in power query (get data) before loading. Given you only have a start date and end date, the best you will get for reporting is monthly hours. I would therefor shape the data to split the hours into monthly buckets before loading (one row per item per month) and then join that to a monthly calendar table.
You will need some custom columns in Power Query to detect the end of month, days in each month, split of hours per month etc before unpivoting.
This is a bit above my understanding level right now, but I think what you're suggesting is to add multiple columns in the query editor that will have the data elements in the date range that I'm interested in, which will then allow me to summarize those elements in visualizations.
Given that I'm dealing with date fields, Is there a good site or resource that covers all of the various date functions that work in Power BI query editor? I'm guessing that I'll need functions to identify the month, identify if a date is at the end of the month, and what days are workdays vs. weekends.
I'm a little flabbergasted that I can do something in excel but cannot replicate the same function in Power BI. This is the formula I use in excel to calculate the prorated hours for a given date range input from the user:
=IF(OR([@[End Date]]<$C$1,[@[Start Date]]>$D$1),0,IF(AND([@[Start Date]]<$C$1,[@[End Date]]<=$D$1),NETWORKDAYS($C$1,[@[End Date]],Holidays)*IF([@Overtime]=TRUE,[@[Scheduled Hours]]/VALUE(SUBSTITUTE([@Duration],"d","")),8),IF(AND([@[Start Date]]>=$C$1,[@[End Date]]>$D$1),NETWORKDAYS([@[Start Date]],$D$1,Holidays)*IF([@Overtime]=TRUE,[@[Scheduled Hours]]/VALUE(SUBSTITUTE([@Duration],"d","")),8),[@[Scheduled Hours]])))
I then do a simple pivot table that summarizes the prorated hours by project and employee.
Can I just have the pivot tables as inputs into Power BI? Will the data come over correctly?
Unfortunatly I don't really understand the Excel version of the formula. I am developing Power Query training now but it wont be ready for a couple of months. I have a couple of sample videos on my site here http://xbi.com.au/pqt and the second one may give you an idea of how to do it. It is a lot easier than Excel as you don't need to know any formulas - just use the menu options.
Tecnically you can import from a pivot table (as currently rendered on a screen) but I don't recommend it. Get the data loaded correctly into Power BI first and then everything is easy. I suggest the file format should be 1 row per month per user with the correct hours for that month.
If you post a workbook (link to Dropbox for example) with some sample real sample data and you clearly explain what the correct split between months is, I will see if I can create a demo for you.
Matt