Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Forecasting future months using YTD Average

Hi there

I was wondering if you can shed some ligth on the following issue.

I have been building the table below using DAX Measures, but my FORECAST measure is not running properly.

 

02. Year-Month (Numeric)MONTHLY ACTUALSACTUALS RUNNING TOTALACTUALS MONTHLY AVERAGEYTD ACTUAL MONTHLY AVERAGEFORECASTPROJECTED ACTUALSPROJECTED ACTUALS RUNNING TOTAL
2021-072,488,049.21 2,488,049.212,488,049.21 2,488,049.21  2,488,049.21 2,488,049.21
2021-082,657,616.365,145,665.572,657,616.362,572,832.79 2,657,616.365,145,665.57
2021-092,471,660.847,617,326.412,471,660.84 2,539,108.80 2,471,660.84 7,617,326.41
2021-103,289.957,620,616.36 3,289.951,905,154.092,539,108.802,542,398.75 10,159,725.16
2021-11-  7,620,616.36  -  1,524,123.272,539,108.802,539,108.8010,159,725.16
2021-12-  7,620,616.36             -  1,270,102.732,539,108.802,539,108.8010,159,725.16
2022-01-  7,620,616.36  -  1,088,659.482,539,108.802,539,108.8010,159,725.16
2022-02-  7,620,616.36               -  952,577.052,539,108.802,539,108.8010,159,725.16
2022-03-  7,620,616.36             -  846,735.152,539,108.802,539,108.8010,159,725.16
2022-04-  7,620,616.36 -  762,061.642,539,108.802,539,108.8010,159,725.16
2022-05-   7,620,616.36                       692,783.31 2,539,108.80 2,539,108.80 10,159,725.16
2022-06-   7,620,616.36-  635,051.36 2,539,108.802,539,108.80 10,159,725.16
Totals 7,620,616.36 7,620,616.36  635,051.36   635,051.362,539,108.80 10,159,725.16 10,159,725.16

 

I would like to use the previous month YTD Monthly Average as the projected value for the following months and calculate a running total to come up with the FY Result.

The table is showing the forecast values in the correct months, but it's not showing the correct total per column.

Logic tells me that I have calculated the Forecast value for one month and that needs to be replicated to the subsequest month, but unsure how to do that.  

I feel that I'm missing something, but I'm teaching myself to use Power BI and to be honest I don't know where to start. 

I have used the following formulas:

 
FORECAST =
CALCULATE(
CALCULATE( [ACTUALS MONTHLY AVERAGE],
FILTER( ALL( 'DATES'),
'DATES'[02. Offset - CurMonth] <= -1 &&
'DATES'[05. Financial Year] = MAX( 'DATES'[05. Financial Year] ) ) ), 'DATES'[02. Offset - CurMonth] >= 0 )
 
******
 
PROJECTED ACTUALS = [MONTHLY ACTUALS] + [FORECAST]

 

******

 

PROJECTED ACTUALS RUNNING TOTAL =
CALCULATE(
    [PROJECTED ACTUALS],
    FILTER(
        ALLSELECTED('DATES'[Date]),
        ISONORAFTER('DATES'[Date], MAX('DATES'[Date]), DESC)
    )
)

 

******

  

I hope you can put me in the right direction.

I look forward to hearing from you.
Cheers
 
Jose
YTD Average
  • Icey's avatar
    Icey
    4 years ago

    Hi Anonymous ,

     

    Try this:

    FORECAST =
IF (
    ISBLANK ( SUM ( 'Fact Table'[MONTHLY ACTUALS] ) ),
    CALCULATE (
        [YTD ACTUAL MONTHLY AVERAGE],
        DATES[YearMonth] < MAX ( DATES[YearMonth] )
    )
)

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    4 years ago

    Thank you Icey.

    I changed my approach and did the calculation a bit different and now is working.

     

     

    Here is the code I used to arrive to the EOFY Expected Result based on YTD Actual Monthly Average:

    FULL YEAR EXPECTED OUTCOME = 
VAR MonthstoEOFY = 
CALCULATE( MEDIAN ( DATES[02. FY Periods Remaining] ) ,
    KEEPFILTERS ( VALUES ( 'DATES'[Date] ) ) ) 

RETURN 
CALCULATE([YTD ACTUAL] + ([YTD ACTUAL MONTHLY AVERAGE] * MonthstoEOFY),
       'DATES'[02. Offset - CurMonth] < 0 )
     

     

    I created a remaining months column in my Dates table to help me with this measure.

    A bit of rounded way, but it is giving me the results I need. So much to learn ğŸ˜…  

    Thank you so much for your help.

    Cheers

    Jose M

2 Replies

  • Icey's avatar
    Icey
    Community Support
    4 years ago

    Hi Anonymous ,

     

    Try this:

    FORECAST =
IF (
    ISBLANK ( SUM ( 'Fact Table'[MONTHLY ACTUALS] ) ),
    CALCULATE (
        [YTD ACTUAL MONTHLY AVERAGE],
        DATES[YearMonth] < MAX ( DATES[YearMonth] )
    )
)

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you Icey.

      I changed my approach and did the calculation a bit different and now is working.

       

       

      Here is the code I used to arrive to the EOFY Expected Result based on YTD Actual Monthly Average:

      FULL YEAR EXPECTED OUTCOME = 
VAR MonthstoEOFY = 
CALCULATE( MEDIAN ( DATES[02. FY Periods Remaining] ) ,
    KEEPFILTERS ( VALUES ( 'DATES'[Date] ) ) ) 

RETURN 
CALCULATE([YTD ACTUAL] + ([YTD ACTUAL MONTHLY AVERAGE] * MonthstoEOFY),
       'DATES'[02. Offset - CurMonth] < 0 )
       

       

      I created a remaining months column in my Dates table to help me with this measure.

      A bit of rounded way, but it is giving me the results I need. So much to learn ğŸ˜…  

      Thank you so much for your help.

      Cheers

      Jose M