cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PatrikBjörnIvar Frequent Visitor
Frequent Visitor

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

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.

3 REPLIES 3
Super User
Super User

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

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

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


PatrikBjörnIvar Frequent Visitor
Frequent Visitor

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

Hello Greg,

 

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

Otherwise is would work, yes. =)

Super User
Super User

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

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!