Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Stacked Report Data / Last Forecast

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/201801/01/201901/02/201901/03/201901/04/201901/05/201901/06/2019
01/01/2019437875459013521365477432730413575623734878
01/02/201943787545901337155746356840000530667125944
01/03/2019437875459013371557290603459816439199173360
01/04/2019437875459013371557290603559816439199173360

 

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

 

Date01/12/201801/01/201901/02/201901/03/201901/04/2019
Current Forecast437875459013371557290603559816
Previous Forecast437875459013521365463568459816

 

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

 

Thanks

 

Need Help

3 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-cherch-msft
        Microsoft Employee
        7 years ago

        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,