Help with comparing Actual vs (multiple) Forecast values

[Gen_tools]

Hello, I'm working on a Power BI report trying to compare actual values with forecasted values.

Our finance team creates a weekly forecast report going 10 weeks out so I'll have up to 10 forecasts for a particular date.

I have multiple categories (disbursements, receipts, net cash daily activity, ect) and I want to find the differences between each forecast and the actual per category to see if our forecasts are getting more accurate as we get closer to the actual date or not. In the example I'm just going to compare 1 category but will eventually like to compare all categories.

All the data is in excel files, we have 1 excel file with the Actual info and we have forecasted data for each week in a separate Forecast Folder (example: Forecast Data 2021-01-12, Forecast Data 2021-01-19, Forecast Data 2021-01-26, ect.). Lots of Power Query transformation going on to get each file setup correctly.

I have a column for each category (Attribute), Column to show what date the forecast was associated with or if its the Actual (Forecasted Date), $ Value (Value), and Date column.

I tried to add a column and do a Calculate function to find the difference between the forecasted date and the actual.

Column = CALCULATE(sum('net cash table'[Value])) - CALCULATE(sum('net cash table'[Value]),'net cash table'[Forecasted Date]="Actual")

but if you can see in the screenshot that the column subtracts the Actual by itself and all the forecasted data isn't subtracting the Actual from the Forecast. I think its because of the calculate function choosing actual and in the visual its showing forecast data which basically makes it Forecast data - 0.

In the screenshot below is how I would like the column to be shown. I'd want it to show the difference between the forecasted date and the actual for a given date/week. I'd also like to show % difference as well but I'm still stuck trying to get the difference.

[MFelix]

Hi @Gen_tools ,

This has to do with context of your calcultion in this case when you create a column and have the 'net cash table'[Forecasted Date]="Actual" what you say is go to column date and check if the value is Actual and subtract it in this case since the value is not actual you don't get the correct value, except on the last line try the following code:

Column =
CALCULATE ( SUM ( 'net cash table'[Value] ) )
    - CALCULATE (
        SUM ( 'net cash table'[Value] ),
        FILTER (
            ALL ( 'net cash table'[Forecasted Date] ),
            'net cash table'[Forecasted Date] = "Actual"
        )
    )

[Gen_tools]

Hello @MFelix Thank you for your reply. I tried out the formula you reccomended unfortunately I was still getting the same result of only the Actual Row subtracting itself.

My data set includes a table for forecast, and a table for actual values. In my post I showed the combined data set to make it easier to view and to see if I can see a simple dax formula solution to my issue.

I was able to come up with a solution by merging queries together. So I joined the Actuals table to my Forecast table based on Attribute and Date matching up and I was able to have an Actuals Value column next to the Forecast Value Column and I created a new column subtracting the Forecast Value from the Actual Value. 

[v-xulin-mstf]

Hi @Gen_tools,

Is your issue solved?
If the issue has been solved, please adopt your solution to help others.
Thanks! 😉

Best Regards,
Link

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