Forum Discussion

Gen_tools's avatar
Gen_tools
Frequent Visitor
5 years ago

Help with comparing Actual vs (multiple) Forecast values

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 forecast...
Need Help
MFelix's avatar
MFelix
Super User
5 years ago

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's avatar
Gen_tools
Frequent Visitor
5 years ago

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's avatar
    v-xulin-mstf
    Community Support
    5 years ago

    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.