Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Backlog hours analysis in Smartsheet & Power BI

Gordon ✭✭✭✭✭
edited 12/09/19 in Archived 2017 Posts

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
    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.

This discussion has been closed.