How to calculate forecast cumulative S-curve shape?

[Mihai_Iso]

Hi,

in power bi, we have a Table, budget cumulative,actual cumulative,but we do not have forecasts - must be calculated; how to calculate with DAX forecast cumulative S-curve shape ? do you have any idea? a sample...

(last actual cumulative value = first forecast cumulative value; last forecast cumulative value = last budget cumulative value)

Thank you!

[amitchandak]

@Mihai_Iso ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[Mihai_Iso]

Hi,

for this simple example, we need to calculate the cumulative forecasts,based on last cumulative actual value 11 and last cumulative budget value 50.So,cumulative forecast must start from date 29/01/20223, and has 11 value,and finally must end on 26/03/2023, having the value 50. And cumulative forecast (or cumulative remaining values) to have an S-curve shape.

We do have,for example:

Cumulative Actual =

IF (

    NOT ISBLANK ( MAX ( 'Table'[actual] ) ),

    CALCULATE (

        SUM ( 'Table'[actual] ),

        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) )

    )

)

And

Cumulative Budget =

CALCULATE (

    SUM ( 'Table'[budget] ),

    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) )

)

How can I find a suitable DAX formula for cumulative remaining, to meet the above requirements?

[Mihai_Iso]

a sample how to look forecast cumulative line shape,more or less...

[amitchandak]

@Mihai_Iso , Try like

CALCULATE (

SUMX( 'Table', if(isblank('Table'[actual] ) , 'Table'[budget] ,'Table'[actual]  )) ,

FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) )

)

refer if needed

How to get a single continuous line for Actual and Target without any break: https://youtu.be/41yhZz2pNTI