Combining sales + forecast in one column; problems with getting the last sales date

[u49947368]

I am trying to show a trendline in which the sales actuals line flows over in a forecast. I found a way to do this online, but get errors in the first part already when I try to obtain the last sales date. 

The instruction video I am using is: https://www.youtube.com/watch?v=DKgF-5QHY68. 

In the first part of the DAX, I need to obtain the last sales date. This formula is not working (I believe) because I have the Sales Amount and the Forecast in the same table, and thereby the last sales date is in fact, the last forecast date (see table below for example):

Finance Date	Sales Amount	Forecast	Budget
10-2021	100	150	120
11-2021	150	200	140
12-2021	0	180	150
12-2022	0	200	120

I also tried to calculate this with a simple IF statement, but although it seems in a table it is working, when I plot it in a line chart it messes up all the data. Example of that IF statement:

Sales + Forecast = IF[Sales Amount] = 0, result if true [Forecast], result if false [Sales Amount]. 

What should I do? Should I change the DAX formula or should I change the data model and split the main table into three different tables (sales table, forecast table, budget table).

Pls help, I am very lost...

[VahidDM]

HI @u49947368 

Try this measure:

Sales + Forecast = 
VAR _MD =
    MAX ( 'Table'[Finance Date] )
VAR _MDS =
    CALCULATE (
        SUM ( 'Table'[Sales Amount] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Finance Date] <= _MD
                && 'Table'[Sales Amount] <> 0
        )
    )
VAR _MDF =
    CALCULATE (
        SUM ( 'Table'[Forecast] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Finance Date] <= _MD
                && 'Table'[Sales Amount] = 0
        )
    )
RETURN
    _MDS + _MDF

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!

LinkedIn: www.linkedin.com/in/vahid-dm/

[u49947368]

Hi VahidDM, 

Thank you for trying to help. This does not work; it gives me very weird results. You can see that in your own result table too. The way it should look like is this: 

Finance Date	Sales Amount	Forecast	Budget	sales + forecast
10-2021	100	150	120	100
11-2021	150	200	140	150
12-2021	0	180	150	180
12-2022	0	200	120	200