cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Sum of remaining amount

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

1 ACCEPTED SOLUTION
Community Support

Hi @tphamAM ,

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

``````Date =
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.

3 REPLIES 3
Super User

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().

New Member

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).

Community Support

Hi @tphamAM ,

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

``````Date =
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.