Forum Discussion

FourAnalysis's avatar
FourAnalysis
New Member
2 years ago
Solved

Simple financial Forecasting measure?

Hi!
I really tried hard for a longer time but now I'm dependend on your help I suppose :).
I'm currently creating a dashboard for monthly finance reportings. It's easy in Excel, but I struggle to implement it into PowerBI.
We want to add a simple Forecasting for our revenue.
It looks like this: 

The difference between Plannend and Reality is added on the Planned-Value of the next month. This is what we call "Forecast".
It's so simple, yet I can't get it to work.

I have the cumulative valies of Planned as:

Cumulative_Planned = CALCULATE([Planned_per month], FILTER(ALLSELECTED('Date-Table'),'Date-Table'[Date]<=MAX('Date-Table'[Date])))

and the same with the Reality values:
Cumulative_Reality = CALCULATE([Reality_Values], FILTER(ALLSELECTED('Date-Table),'Date-Table'[Date]<=MAX('Date-Table'[Date])))

I tried to get nearer to the forecasting value by creating some measures:
Forecasting_Difference =  [Reality_values] - [Planned_per_month]     ///now I have the difference
 
Cumulative_Forecasting_Difference = CALCULATE([Forecasting_Difference], FILTER(ALLSELECTED('Date-Table'),'Date-Table'[Date]<=MAX('Date-Table'[Date])))   /// now I have the differences cumulative
 
Forecasting = [Cumulative_Forecasting_Difference] + [Cumulative_Planned] // now I have the forecasting value

HOWEVER, PowerBI starts subtracting the difference between Planned and Reality in january already, so everything is mixed up a little bit.

 

 

 

What it should be:

I'd appreciate your help alot, as I really put more work into what seems as a really easy problem but for me (as a PowerBI beginner) it's really not!

Thanks!

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi FourAnalysis 

     

    Please try this measure:

    Forecasting =
VAR _vtable =
    SUMMARIZE (
        ALLSELECTED ( 'Date-Table' ),
        'Date-Table'[Month],
        'Date-Table'[Month Number],
        "__Planned", [Planned],
        "__Difference", [Forecasting Difference]
    )
VAR _vtable2 =
    ADDCOLUMNS (
        _vtable,
        "__Outcome",
            IF (
                MAXX (
                    FILTER ( _vtable, [Month Number] = EARLIER ( 'Date-Table'[Month Number] ) - 1 ),
                    [__Difference]
                )
                    <> BLANK (),
                [__Planned]
                    + MAXX (
                        FILTER ( _vtable, [Month Number] = EARLIER ( 'Date-Table'[Month Number] ) - 1 ),
                        [__Difference]
                    )
            )
    )
RETURN
    MAXX (
        FILTER ( _vtable2, [Month] = SELECTEDVALUE ( 'Date-Table'[Month] ) ),
        [__Outcome]
    )

     

    I create a set of sample, and the result is as follow:

     

     

    Best Regards

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

2 Replies

  • FourAnalysis's avatar
    FourAnalysis
    New Member
    2 years ago

    Thanks alot! It looks much more complicated than I have thought it would look like.
    Would not been able to have done this without your help, thanks alot!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi FourAnalysis 

     

    Please try this measure:

    Forecasting =
VAR _vtable =
    SUMMARIZE (
        ALLSELECTED ( 'Date-Table' ),
        'Date-Table'[Month],
        'Date-Table'[Month Number],
        "__Planned", [Planned],
        "__Difference", [Forecasting Difference]
    )
VAR _vtable2 =
    ADDCOLUMNS (
        _vtable,
        "__Outcome",
            IF (
                MAXX (
                    FILTER ( _vtable, [Month Number] = EARLIER ( 'Date-Table'[Month Number] ) - 1 ),
                    [__Difference]
                )
                    <> BLANK (),
                [__Planned]
                    + MAXX (
                        FILTER ( _vtable, [Month Number] = EARLIER ( 'Date-Table'[Month Number] ) - 1 ),
                        [__Difference]
                    )
            )
    )
RETURN
    MAXX (
        FILTER ( _vtable2, [Month] = SELECTEDVALUE ( 'Date-Table'[Month] ) ),
        [__Outcome]
    )

     

    I create a set of sample, and the result is as follow:

     

     

    Best Regards

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