Forum Discussion
Summarize data between dates
I'm just getting started on Power BI, so please excuse the stupid question. I have a project assignment table with total hours, employee, start and end date fields in it and I would like to summarize the total hours by employee and time period with proper cutoffs and proration of the total hours by month. The built in visualizations seem to be only based on one date field, but the hours could span across months.
For example, a project might start 1/23/17 and end 2/3/17 and have 80 total hours, but I want to see the hours in the date drill down separated as 56 hours in January and 24 hours in February in the chart visual.
Help!
6 Replies
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?
