My PBI model is supposed to report at the mid month point but it needs to show a full month value which is made up the first three weeks of actual hours PLUS two weeks that is extrapolated as a straight line average based on the 3 weeks of actuals. So, if actual data exists it should be reported but where the last couple of weeks doesn’t have any data in the dataset the DAX calculation should create it, does that make sense?
I have included a mock up result in the attached excel workbook.
I have tried several approaches and tried to adapt Sam’s forecasting sales logic but I am getting in a tangle, you can see the attempts I have made in the pbix file, would be grateful for any ideas on how to calc forecast hours for weeks in the future, beyond the date range in the dataset,
Many thanks Lizzie Forecast Hours.pbix (94.2 KB) Test Forecast Hours.xlsx (40.1 KB)
Hi Rajesh
Thank you very much for the DAX, something weird is happening that I can’t figure out, when I transfer the DAX to my client PBIX the outcome isn’t the same, I have ensured “Show items with no data” is checked, I have checked the relationship is the same as my test pbix, the transactional source data is identical.
The real life model is more complex but I can’t see how other dimension tables could alter things ( see model snip)
Can you suggest what else could be giving me a different result? Many thanks Lizzie
Hi Rajesh
Just to let you know that over the weekend I recreated my report from scratch and the measure you supplied worked fine so the existing pbix must have been corrupted at some point - thank you for providing the solution, Lizzie
