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