ytd average
1 Topic
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 ACTUALS ACTUALS RUNNING TOTAL ACTUALS MONTHLY AVERAGE YTD ACTUAL MONTHLY AVERAGE FORECAST PROJECTED ACTUALS PROJECTED ACTUALS RUNNING TOTAL 2021-07 2,488,049.21 2,488,049.21 2,488,049.21 2,488,049.21 2,488,049.21 2,488,049.21 2021-08 2,657,616.36 5,145,665.57 2,657,616.36 2,572,832.79 2,657,616.36 5,145,665.57 2021-09 2,471,660.84 7,617,326.41 2,471,660.84 2,539,108.80 2,471,660.84 7,617,326.41 2021-10 3,289.95 7,620,616.36 3,289.95 1,905,154.09 2,539,108.80 2,542,398.75 10,159,725.16 2021-11 - 7,620,616.36 - 1,524,123.27 2,539,108.80 2,539,108.80 10,159,725.16 2021-12 - 7,620,616.36 - 1,270,102.73 2,539,108.80 2,539,108.80 10,159,725.16 2022-01 - 7,620,616.36 - 1,088,659.48 2,539,108.80 2,539,108.80 10,159,725.16 2022-02 - 7,620,616.36 - 952,577.05 2,539,108.80 2,539,108.80 10,159,725.16 2022-03 - 7,620,616.36 - 846,735.15 2,539,108.80 2,539,108.80 10,159,725.16 2022-04 - 7,620,616.36 - 762,061.64 2,539,108.80 2,539,108.80 10,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.80 2,539,108.80 10,159,725.16 Totals 7,620,616.36 7,620,616.36 635,051.36 635,051.36 2,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 JoseSolved4.4KViews0likes2Comments