Add cumulative forecast on a cumulative sales

[Anonymous]

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.

How to do it?

[OwenAuger · Accepted solution]

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

[Anonymous]

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