Cumulative forecast to continue after cumulative actual

[Mihai_Iso]

Hello folks!

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...

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)

    )

)

------------------------------------------------------------------

Cumulative Actual + Forecast measure:

 Cumulative A+F =

VAR LastSalesDate =

CALCULATE(

    MAX(Actual[Actual Finish]),

    REMOVEFILTERS(DateTable)

)

VAR Result =

IF(

    MAX(DateTable[Date])>=LastSalesDate,

    [Forecast Cumulative],

    [Actual cumulative]

)

RETURN

Result

-------------------------------------------------------

I cannot plot Cumulative A+F  (Cumulative actual+forecast) measure, after Actual we have  a jump down to first forecast cumulative value…

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

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.

Can you help me,please?

Thank you!

Best regards.

[Mihai_Iso]

I think the issue is how to make [Forecast Cumulative] to start from last actual cumulative value.If I put last actual cumulative value 1980 + [Forecast Cumulative] , everything is ok.But I need help, maybe to set a variable called "lastactualvalue" and get this maximum actual value as scalar.

What do you think?

---------------------------------------------------------

Cumulative Actual + Forecast measure:

 Cumulative A+F =

VAR LastSalesDate =

CALCULATE(

    MAX(Actual[Actual Finish]),

    REMOVEFILTERS(DateTable)

)

VAR Result =

IF(

    MAX(DateTable[Date])>=LastSalesDate,

   1980 + [Forecast Cumulative],

    [Actual cumulative]

)

RETURN

Result

[Mihai_Iso · Accepted solution]

Measure 22 = [Forecast Cumulative]+[Last known actual value 1]

Last known actual value 1 =

CALCULATE([Actualcumulative],filter(datesbetween(DateTable[Date],minx(all(DateTable),DateTable[Date]),max(DateTable[Date])),LASTNONBLANK(DateTable[Date],CALCULATE([Actual cumulative]))))

Forecast Cumulative =

CALCULATE(

    [Forecast Date],

    FILTER(

        ALLSELECTED('DateTable'[Date]),

        ISONORAFTER('DateTable'[Date], MAX('DateTable'[Date]), DESC)

    )

)

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])))))

Line y-axis: “Measure 22” must be above measure “Actual cumulative” ! in this way will be visible both !

---------------------------------------------------------------------------------------------