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
Anonymous
Not applicable

Add cumulative forecast on a cumulative sales

Hello! I have a Cumulative Sales (Pink) and want to add my cumulative forecast (Black) so the starting point of the forecast =ending value of sales.

 

Sales and forecast are in different tables, and it's not an option to join.

 

 

Cumulative.PNG

 

 

How to do it?

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hello @Anonymous 

 

One method would be: find the last nonblank value of Cumulative Sales and add that to the existing Cumulative Forecast.

 

As an example, if you have:

  • A Date table with column Date[Date] which is marked as a date table, and other tables appropriately related to Date
  • Existing measures [Cumulative Sales] and [Cumulative Forecast]

then the measure could look like this:

Cumulative Forecast Shifted =
VAR LastCumulativeSales =
    CALCULATE (
        [Cumulative Sales],
        LASTNONBLANK ( ALL ( 'Date'[Date] ), [Cumulative Sales] )
    )
VAR CumulativeForecast = [Cumulative Forecast]
RETURN
    IF (
        NOT ISBLANK ( CumulativeForecast ),
        LastCumulativeSales + CumulativeForecast
    )

There may be some tweaking required based on your data model but that's the logic I would use.

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

Hello @Anonymous 

 

One method would be: find the last nonblank value of Cumulative Sales and add that to the existing Cumulative Forecast.

 

As an example, if you have:

  • A Date table with column Date[Date] which is marked as a date table, and other tables appropriately related to Date
  • Existing measures [Cumulative Sales] and [Cumulative Forecast]

then the measure could look like this:

Cumulative Forecast Shifted =
VAR LastCumulativeSales =
    CALCULATE (
        [Cumulative Sales],
        LASTNONBLANK ( ALL ( 'Date'[Date] ), [Cumulative Sales] )
    )
VAR CumulativeForecast = [Cumulative Forecast]
RETURN
    IF (
        NOT ISBLANK ( CumulativeForecast ),
        LastCumulativeSales + CumulativeForecast
    )

There may be some tweaking required based on your data model but that's the logic I would use.

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

Thank you so much @OwenAuger - exactly what I needed!

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.