How to link forecast cumulative with actual cumulative

[Mihai_Iso]

Hello,

I want to have forecast cumulative line linked with actual cumulative line.I have made two measures-actual cumulative and forecast cumulative,and next step should be to link these two cumulative lines in the same graph...

Can you help me,please?

Thank you!

Best regards.

[amitchandak]

@Mihai_Iso , Add these two measures and create a new one

m3 = [Forecase Cumm] +[Actual Cumm]

[Mihai_Iso]

Measures

Actual Date = count(Actual[Actual Finish])

Forecast Date = count(Forecast[Forecast Finish])

Actual cumulative ( stop after last value)

Actual cumulative =

VAR _End = CALCULATE ( MAX ( Actual[Actual Finish] ), ALL ( 'DateTable' ) )

RETURN

    IF (

        _End < MIN ( 'DateTable'[Date] ),

        BLANK (),

CALCULATE(

    COUNTA(Actual[Actual Finish]), USERELATIONSHIP(Actual[Actual Finish],'DateTable'[Date]),

    FILTER(

        ALLSELECTED('DateTable'[Date]),

        ('DateTable'[Date] <= MAX('DateTable'[Date])))))

Forecast cumulative

Forecast Cumulative =

CALCULATE(

    [Forecast Date],

    FILTER(

        ALLSELECTED('DateTable'[Week no. real]),

        ISONORAFTER('DateTable'[Week no. real], MAX('DateTable'[Week no. real]), DESC)

    )

)

B_Actual =

 var lastactual =

  calculate(

    MAX(Actual[Actual Finish]),

    REMOVEFILTERS(DateTable)

  )

 var Actuals= [Actual cumulative]

 var FutureForecasts=

  calculate(

    [Forecast Cumulative],

    FILTER(

        values(DateTable),

         DateTable[Date]>lastactual)

  )

 var Result = Actuals + FutureForecasts

 Return

 max(DateTable[Date])

As you can see in the table, B_Actual has dates,not values…

Thank you.

best regards,

Mike.

[Mihai_Iso]

"B_Actual"(I put a wrong description) is in fact another measure which contain actual and forecast cumulative (combined) line.

 But as I have explained to you, in case we have data(counting) does not work...work only if we have values(numbers).

Anyone have an idea how to solve this?

Thank you in advance!

[Mihai_Iso]

If you have dates,not values ("Sales amount" is famous...),it is quite difficult to get the correct results.

Guys,anyone who can help me?

[Mihai_Iso]

Hi,

Thank you for your advice.But it is still an issue,please find below

I need also to have both cumulative in the  graph, to see against cut-off (week 35,in this case).

[Mihai_Iso]

Hi, be aware that there are no numbers(values), there are dates,counted dates,please see below.Cumulative counted dates by week,for plan,actual and forecast.

[Mihai_Iso · Accepted solution]

Please see below solution

https://community.powerbi.com/t5/Desktop/Cumulative-forecast-to-continue-after-cumulative-actual/m-p...