Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mihai_Iso
Helper II
Helper II

How to link forecast cumulative with actual cumulative

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.

 

 

Mihai_Iso_0-1674187318043.png

 

Mihai_Iso_1-1674187362373.png

 

1 ACCEPTED SOLUTION
7 REPLIES 7
amitchandak
Super User
Super User

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

 

m3 = [Forecase Cumm] +[Actual Cumm]

Mihai_Iso_0-1674275258617.png

Mihai_Iso_1-1674275273465.png

Mihai_Iso_2-1674275288079.png

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)

    )

)

 

Mihai_Iso_3-1674275311108.png

 

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.

"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!

 

 

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?

 

Hi,

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

Mihai_Iso_0-1674191756236.png

 

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

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_0-1674192176264.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.