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.
