Stacked Report Data / Last Forecast

[Anonymous]

My raw data contains stacked montly reports that are updated each month with the months updated actuals and new forecast. Since the 'forecasts' of realised actuals become equal I am looking a 'previous forecast' series  takes the forecast for the previous month at each datapoint, i.e the forecast the month before the actual was realised. I have included an example below.

Date Report Date	01/12/2018	01/01/2019	01/02/2019	01/03/2019	01/04/2019	01/05/2019	01/06/2019
01/01/2019	437875	459013	521365	477432	730413	575623	734878
01/02/2019	437875	459013	371557	463568	400005	306671	25944
01/03/2019	437875	459013	371557	290603	459816	439199	173360
01/04/2019	437875	459013	371557	290603	559816	439199	173360

The figure highlighted in bold should make up my new series as the forcast from the previous report before that date.

Date	01/12/2018	01/01/2019	01/02/2019	01/03/2019	01/04/2019
Current Forecast	437875	459013	371557	290603	559816
Previous Forecast	437875	459013	521365	463568	459816

The fields in question are:  [Report_Date], [Date] and [Current Forecast]

Thanks

[v-cherch-msft]

Hi @Anonymous 

You may try below measure:

Previous Forecast =
IF (
    MAX ( Table1[Date] ) <= MINX ( ALL ( Table1 ), Table1[Report Date] ),
    CALCULATE ( MAX ( Table1[Value] ) ),
    CALCULATE (
        SUM ( Table1[Value] ),
        FILTER (
            Table1,
            Table1[Report Date]
                = DATE ( YEAR ( Table1[Date] ), MONTH ( Table1[Date] ) - 1, DAY ( Table1[Date] ) )
        )
    )
)

Regards,

[Anonymous]

Thanks @v-cherch-msft 

I amended your code slightly to the following, which is closer to the solution I require as pulls in last months figures, however going forward I need to use the the value that reflects the earliest available report.

Previous Forcast Forum = 
IF (
    MAX(‘Table1’[Date]) <= MINX( ALL(‘Table1’), ‘Table1’[Report Date ]),
    CALCULATE(SUM(‘Table1’[Value])),
    CALCULATE(SUM(‘Table1’[Value]), DATEADD(‘Table1’[Report Date ],-1,MONTH)
            )
)

I presently get the following result, however need the value where the report date = date, to be equal across all reports.

[v-cherch-msft]

Hi @Anonymous 

Could you share some data sample and expected output? You can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

Regards,