Skip to main content
cancel
Showing results for 
Search instead 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

Reply
tphamAM
New Member

Sum of remaining amount

Hi all, 

IDDateAmount
106/20201,000
 07/20202,000
209/20203,000
 10/20204,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/202010,0001,000 + 2,000 + 3,000 + 4,000 
07/20209,0002,000 + 3,000 + 4,000
08/20207,0003,000 + 4,000
09/20207,0003,000 + 4,000
10/20204,0004,000
11/202000

 

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/20201,000Item 1 only
07/20202,000Item 1 only
08/20200Item 1 only
09/20207,000 
10/20204,000 
11/20200 

 

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

1 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" )
)

vkkfmsft_0-1631251615900.png

 

Then try the following measure:

 

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

vkkfmsft_1-1631251802511.png

 

 

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.

View solution in original post

3 REPLIES 3
lbendlin
Super User
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().

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

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

vkkfmsft_0-1631251615900.png

 

Then try the following measure:

 

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

vkkfmsft_1-1631251802511.png

 

 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors