Issue calculating Running total by columns

[martinfernandez]

Hello,

I have a table with two main columns and a measure:

Rows: Vendor name
Columns: Transaction Date

Values: Accounting Balance

I need to create a matrix in Power BI with this data and the accounting balance added to the previous value.

As a case scenario I will use this example where the totals are calculated vertically (as usual):

This is what I really need:

A brief explanation of the previous table:

1. Vendor 1 owed $200 on January 2017
2. Vendor 1 owed $0 on February 2017 because he made a $200 payment
3. Vendor 1 owed $50 on March 2017 because $0 + $50
4. Vendor 1 owed $50 on April 2017 because he didn't make any payment.
5. Vendor 1 owes $50 in total.
6. etc

In other words I need to calculate the payments made by vendor for each month / column totals (that he owes in total). The last month (the current) will have the same debt as the total because it is cumulative.

Any suggestions? Thanks in advance

[v-jiascu-msft]

Hi @martinfernandez,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[v-jiascu-msft]

Hi @martinfernandez,

What does the original data look like? Please refer to the demo in the attachment. 

1. If you have a date table, you can use this one.

Measure 3 =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] <= MAX ( 'Table1'[Date] ) )
)

2. If you don't have a date table, you can try this one.

Measure =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER (
        ALL ( Table1 ),
        'Table1'[Date] <= MAX ( 'Table1'[Date] )
            && 'Table1'[Vendor] = MIN ( 'Table1'[Vendor] )
    )
)

Best Regards,
Dale