DAX help to calculate measure remaining forecast

[TrevLc]

Hi, I am struggling to work out a calulation.

Basically I need remaining forecast per "parent project"

In the example data below, firstly I have cumulative amounts only in my source, which is confusing me.

I also have actuals and forecasts in one column.

I can calculate actuals and forecast per project, but remaining forecast for the parent project is getting me. (especially the "total row")

So, for example, Parent Project "A" has 2 sub projects "Fix roads" and "build trains"

Fix roads has remaining forecast of 50 (latest month forecast minus previous month actuals)

build trains has remaining forecast 80 (latest month forecast minus previous month forecast (not this project does not have previous month actuals - plenty of projects dont))

I want to show total remaining forecast of 130 (80 + 50 ) for parent project A, but it is calculating incorrectly.

I show my data on a table visualisation per parent project.

Here is some example data:

Parent	Project	Version	Date	Cumulative Amount
A	Fix roads	Actuals	1/04/2023	100
A	Fix roads	Actuals	1/05/2023	150
A	Fix roads	Forecast	1/06/2023	200
A	build trains	Forecast	1/05/2023	80
A	build trains	Forecast	1/06/2023	160
B	New Bikes	Actuals	1/05/2023	100
B	New Bikes	Forecast	1/06/2023	120

Appreciate your help

[TrevLc]

Thanks so much.

It works (although I don't completely understand why).

However, if I add date into the table, it doesn't work.

I only want to see the latest remaining forecast (previous forecasts are no longer relevant)

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[TrevLc]

Thank you.

The dataset is per the original question, and the outcome is very similar to what you have already shown. I have mocked up what I expect below.

Appreciate your help.

[lbendlin · Accepted solution]

That is a rather unfortunate way of trying to displ;ay your data.  I am sure this will lead to user confusion.  (I don't agree with summarizing the cumulative values, for example).

Here is the modified measure.

Remaining Forecast =
VAR a =
    SUMMARIZE(
        'Table',
        [Parent],
        [Project],
        [Date],
        "ca", SUM( [Cumulative Amount] )
    )
VAR b =
    ADDCOLUMNS(
        a,
        "md", CALCULATE( MAX( 'Table'[Date] ), REMOVEFILTERS( 'Table'[Date] ) )
    )
VAR c =
    ADDCOLUMNS(
        b,
        "pd",
            VAR md = [md]
            RETURN
                CALCULATE(
                    MAX( 'Table'[Date] ),
                    'Table'[Date] < md
                )
    )
VAR d =
    ADDCOLUMNS(
        c,
        "pda",
            VAR pd = [pd]
            RETURN
                IF(
                    [Date] = [md],
                    [ca]
                        - CALCULATE(
                            SUM( 'Table'[Cumulative Amount] ),
                            'Table'[Date] = pd
                        )
                )
    )
RETURN
    SUMX( d, [pda] )

[TrevLc]

That is perfect.

Thanks so much.

I won't be showing cumulative $ in my report, I was just showing it in my screenshot.

[lbendlin]

see attached