Backlog hours analysis in Smartsheet & Power BI

Gordon

I need help with a solution to analyze and report on backlog hours by month, based on project tasks maintained in smartsheet.  Currently, we have all our project tasks in separate sheets in Smartsheet, with individual rows for each task that include a start date, end date, duration (in days), assigned to, and scheduled hours.  The data from multiple sheets is then aggregated via a report for all scheduled tasks for a given future time period (based on start and end date parameters in the report builder), which then is exported to excel for further formulas to calculate scheduled hours for a selected month (start and end period in Smartsheet often crosses over month end, but I need to determine monthly cutoff of hours).

 

I also have the Power BI integration, working which works well for single date field analysis but I don't know how to take the start and end date in Smartsheet to produce accruate results in Power BI, aside from my excel formula based workaround.

 

I probably didn't explain it very well, but I think my problem is pretty common, so I'm hoping that someone else may have some insight and recommendations.

Gordon

I guess what I'm really asking for is a way to disaggregate the single row of data from the smartsheet report to multiple rows, one for each date between the start and end date.  Power BI's visualizations do not seem to work with date range data at all.

 

I'm thinking that this is beyond Smartsheet's capabilities and will need to be done in excel and then inserted into Power BI.