Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Forecast in table not relating to date

Hi,

I am trying to do a simple forecast by using the average of monthly units sold over a period of several years. The way I am doing it is by simply looking across the historical sales at the month row context and doing an average of the units.

 

pbi.jpg

 
 

 

The result is correct however it has no year relevance, i.e., the month of January average has no connection to any year.

What I would like to do is use my FactSales table and calculate the forecast forward for any periods in the future (after the last reported sale). That way I could dynamically have the report update the forecast by replacing the forecast with the current sales as they occur and forecast the data out relative to the last sale (i.e. 12 months forward).

My measure to forecast is:


Forecast Units =
AVERAGEX(
VALUES( ‘Calendar’[MonthYear]),
[Total Qty])

 

So obviously the formula has no connection to a future date. Kind of like the way forecasting in Excel works - the actual sales column has data and as soon as the last amount occurs trhe forecast column begins populating for the next periods. I have attached a sample PBIX file with my data.

 

Any help would be greatly appreciated. I have looked at other versions of forecasts and none seem to work for me. Once I get this solved, the next task is to try different forecasting formulas like weighted average, exponential triple smoothing, etc., so any suggestions on that would be very helpful as well. Thanks so much in advance!

3 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

Hi @Anonymous ,

 

First of all you need to have your calendar go all the way up until 2020 since you only have it until 2019 you will not get the amount you want.

 

Then you need to create a measure similar to this one:

 

Measure =
IF (
    MAX ( 'Calendar'[Date] ) <= EOMONTH ( TODAY (); 0 );
    [Total Qty];
    CALCULATE (
        SUMX ( 'Calendar'; [Total Qty] );
        FILTER (
            ALL ( 'Calendar' );
            'Calendar'[Date]
                <= MIN ( 'Calendar'[Date] ) - 1
                && 'Calendar'[Date]
                    >= MIN ( 'Calendar'[Date] ) - 365
        )
    ) / 12
)

 

The calculation checks if the end of month of current date is part of the date table if yes return the sum of unit if not makes an estimation.

 

On this case I'm summing the  values of the last 12 months and dividing by twelve so a simple average but you can adjust to whatever you want being the important part the filter of the dates:

FILTER (
            ALL ( 'Calendar' );
            'Calendar'[Date]
                <= MIN ( 'Calendar'[Date] ) - 1
                && 'Calendar'[Date]
                    >= MIN ( 'Calendar'[Date] ) - 365
        )

Also here you can adjust to any time frame you need.

 

Check PBIX file attach.

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Hi @Anonymous ,

 

As I refered that was one approach to the forecasting model this can have several ones. Looking at your request you need to make the following steps:

 

  • Create a calendar table to make the filter for the forecasting (in the file attach I call it DimDate)
  • Make the following measure:
Forecast Revised = 
IF (
    MAX ( 'Calendar'[Date] ) <= EOMONTH ( TODAY (); 0 );
    [Total Qty];
    CALCULATE (
        SUMX ( 'Calendar'; [Total Qty] / DISTINCTCOUNT ( 'Calendar'[MonthYear] ) );
        FILTER (
            ALL ( 'Calendar' );
            'Calendar'[Date] <= MAX ( DimDate_Filter[Date] )
                && 'Calendar'[Date] >= MIN ( DimDate_Filter[Date] )
                && 'Calendar'[MonthNum]
                    = LOOKUPVALUE (
                        DimDate_Filter[MonthNum];
                        DimDate_Filter[MonthNum]; MIN ( 'Calendar'[MonthNum] )
                    )
        )
    )
)

 

Should give expected result.

 

See attach file.

 

Regards,

MFelix

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Hi @Anonymous ,

 

Using a single table to make the selection of the period you want to analyze and the months were you want to make the forecast be consider will not work because you would be filtering the information on the same data.

 

Assuming that you want to see the information from Jan-17 until Dec-20 you would make the selection of this on your filter but since 2020 is not actuals yet and you want to have the forecast on those months you would select for the forecast Jan 17 - Dec 18  based on this your first table would be filter out until Dec 18 not giving you the expected result since on your second filter you would take out the 19 actuals and the 20 column for the forecast.

 

Basically using the same table for filtering your data when you applied the second filter would make a subset of the first information.

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
MFelix
Super User
Super User

Hi @Anonymous ,

 

First of all you need to have your calendar go all the way up until 2020 since you only have it until 2019 you will not get the amount you want.

 

Then you need to create a measure similar to this one:

 

Measure =
IF (
    MAX ( 'Calendar'[Date] ) <= EOMONTH ( TODAY (); 0 );
    [Total Qty];
    CALCULATE (
        SUMX ( 'Calendar'; [Total Qty] );
        FILTER (
            ALL ( 'Calendar' );
            'Calendar'[Date]
                <= MIN ( 'Calendar'[Date] ) - 1
                && 'Calendar'[Date]
                    >= MIN ( 'Calendar'[Date] ) - 365
        )
    ) / 12
)

 

The calculation checks if the end of month of current date is part of the date table if yes return the sum of unit if not makes an estimation.

 

On this case I'm summing the  values of the last 12 months and dividing by twelve so a simple average but you can adjust to whatever you want being the important part the filter of the dates:

FILTER (
            ALL ( 'Calendar' );
            'Calendar'[Date]
                <= MIN ( 'Calendar'[Date] ) - 1
                && 'Calendar'[Date]
                    >= MIN ( 'Calendar'[Date] ) - 365
        )

Also here you can adjust to any time frame you need.

 

Check PBIX file attach.

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

OMG @MFelix !!! You are the best! I can't believe you got this. Really, thank you so much for your help.

 

I do have another question, if you don't mind. The forecast method you used, the average of the last 12 months, really doesn't work for me. I have a date slicer where I can select a range of years to analyze and forecast and I really need the forecast to take the values for each month and average them across the years, i.e., average all the Januarys, Februarys, etc., for the selected year ranges. Would you be kind enough to suggest a modification for this? At some point I'll probably modify that formula again to make it a more complex and complete forecast but for now this will be amazing.

 

Again, I can't thank you enough for your help. I hope I can get to your level one day...it has been a quite a learning curve.

 

Best regards,

Jeremy

Hi @Anonymous ,

 

As I refered that was one approach to the forecasting model this can have several ones. Looking at your request you need to make the following steps:

 

  • Create a calendar table to make the filter for the forecasting (in the file attach I call it DimDate)
  • Make the following measure:
Forecast Revised = 
IF (
    MAX ( 'Calendar'[Date] ) <= EOMONTH ( TODAY (); 0 );
    [Total Qty];
    CALCULATE (
        SUMX ( 'Calendar'; [Total Qty] / DISTINCTCOUNT ( 'Calendar'[MonthYear] ) );
        FILTER (
            ALL ( 'Calendar' );
            'Calendar'[Date] <= MAX ( DimDate_Filter[Date] )
                && 'Calendar'[Date] >= MIN ( DimDate_Filter[Date] )
                && 'Calendar'[MonthNum]
                    = LOOKUPVALUE (
                        DimDate_Filter[MonthNum];
                        DimDate_Filter[MonthNum]; MIN ( 'Calendar'[MonthNum] )
                    )
        )
    )
)

 

Should give expected result.

 

See attach file.

 

Regards,

MFelix

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Thanks so much, @MFelix ! It works!

 

I am curious why we needed to make DimDate table for the filter and not use the existing date table? Sorry, I'm a bit of a newbie and I'm still trying to figure this all out.

 

Again, thanks so much for your time and great help.

 

Best regards,

 

Jeremy

Hi @Anonymous ,

 

Using a single table to make the selection of the period you want to analyze and the months were you want to make the forecast be consider will not work because you would be filtering the information on the same data.

 

Assuming that you want to see the information from Jan-17 until Dec-20 you would make the selection of this on your filter but since 2020 is not actuals yet and you want to have the forecast on those months you would select for the forecast Jan 17 - Dec 18  based on this your first table would be filter out until Dec 18 not giving you the expected result since on your second filter you would take out the 19 actuals and the 20 column for the forecast.

 

Basically using the same table for filtering your data when you applied the second filter would make a subset of the first information.

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Ahhh...smart...I get it. Thanks so much for your reply and all your help. It is very much appreciated. I know I asked for a lot and you have been super gracious.

 

Thanks, again, and best regards,

 

Jeremy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.