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 'previo...
Need Help
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,