Linear forecast based on historical values (month, quarter, year)

[PatrikBjörnIvar]

Hello!

I've 2 tables in PBI model:

1) Granular daily sales values (by products, customers, etc.) with missing dates in between (sales don't happen every day)

2) A list of dates with 3 columns:

• Date (no missing dates)
• Month-to-date number of working days for that date
• Total number of working days for that month

I need a graph with 2 lines:

1. Sales, MTD

2. Linear forecast for sales based on this formula: ( Sales, MTD / Month-to-date number of working days for that date

) * Total number of working days for that month

This should preferably work for quarter and year.

Would appreciate any help.

Thank you in advance.

[Greg_Deckler]

You should be able to use my Quick Measure here for Net Work Days:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

[PatrikBjörnIvar]

Hello Greg,

unfortunatly, the challange is not in calculating the number of working days.

Otherwise is would work, yes. 😃

[Greg_Deckler]

OK, please post sample data to recreate the situation. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490