Forum Discussion
Summarize data between dates
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?