Sum of remaining amount

[tphamAM]

Hi all, 

ID	Date	Amount
1	06/2020	1,000
	07/2020	2,000
2	09/2020	3,000
	10/2020	4,000

I am currently have the above table, which represent the monthly amount for different item (column 1). I would need to get a the monthly remaining amount for all & each items like below:

As at	Remaining amount	Note
06/2020	10,000	1,000 + 2,000 + 3,000 + 4,000
07/2020	9,000	2,000 + 3,000 + 4,000
08/2020	7,000	3,000 + 4,000
09/2020	7,000	3,000 + 4,000
10/2020	4,000	4,000
11/2020	0	0

I tried to use the below DAX formula: 

Remaining amount =

CALCULATE(SUM(Amount),

Date>EARLIER(Date)

&&ID=EARLIER(ID)

)

The problem is that there is no row in 06/2020 for item 2, so as at 06/2020, I am not able to take in the remaining amount for item 2, so my current table is wrong like this:

Month	Remaining amount	Note
06/2020	1,000	Item 1 only
07/2020	2,000	Item 1 only
08/2020	0	Item 1 only
09/2020	7,000
10/2020	4,000
11/2020	0

Not sure what else I can do, thank you in advance

[lbendlin]

You need to clean up your source data.  In Power Query use the "Fill Down" transformation on the ID column.  Then you can apply the formulas as needed.

Your use case is interesting - looks like what you need is the exact opposite of TOTALYTD().

[tphamAM]

Thank you.

I can fill down when clean up the data, but I think the core issue is that there is no row data for item if it has not started (e.g. filter will not be able to capture item 2 for months prior to 09/2020). The remaining of these items are not taken into the formula at all. 

I found a workaround, which is to create a row for each relevant month for each item. But this will result in a very big data set (e.g. 36 lines for each item for a 3 year period). 

[v-kkf-msft · Accepted solution]

Hi @tphamAM ,

Please create the following Date table and inter-table relationship.

Date = 
ADDCOLUMNS (
    CALENDAR (
        EOMONTH ( MIN ( 'Table'[Date] ), -1 ) + 1,
        EOMONTH ( MAX ( 'Table'[Date] ), 0 )
    ),
    "MonthYear", FORMAT ( [Date], "mm/yyyy" )
)

Then try the following measure:

Remaining amount = 
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[ID] ),
        'Table'[Date] >= MIN ( 'Date'[Date] )
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.