Total Outstanding on each day

[stefansi]

Hi,

My manager has asked me to produce a table in Power BI that sums our firm's outstanding claim on our processing bank at each processing date (and in each currency). The problem is the following (simplified version of the data):

I have a table from the bank with raw data – six columns (A-F). The bank will process transactions each business day morning (the processing date - column B) and pay us that amount two business days later (on the value date - column D). Thus, on each processing day we will have two value dates outstanding. As an example, using EUR in the table, on 15 September (cell B4) the value dates of 16 and 17 September are still outstanding (cells D2, D4, F2 and F4). This would sum to a EUR 2 million oustanding claim (cell G4) in column G, the column I need to create. This is easy to do in excel but the table containing the raw data in columns A-F is stored in a SQL Server which I query via Power Query, and I can't figure out how to do this there and publish it in Power BI Service.

And just to add, this doesn't have to be an additional column in this particular table. This may as well just be a new table with two columns, one for the processing date and another for the outstanding claim. Whichever works best.

I would appreciate all help!

Sidenotes:

1. I highlighted Friday 18 September just to show how it works over weekends (emphasize that we're using working days here).
2. Commas are used as a thousand separator in my country.
3. This is a simplified version of the problem. There are 15 currencies in total.

Thank you.

[V-lianl-msft · Accepted solution]

Hi @stefansi ,

Refer to this sample:

Measure =
VAR CURRENT_LINE_DATE =
    MAX ( 'TABLE'[PD] )
VAR oc =
    CALCULATE (
        SUM ( 'TABLE'[AMOUNT] ),
        FILTER (
            ALL ( 'TABLE' ),
            SUMX (
                FILTER (
                    'TABLE',
                    EARLIER ( 'TABLE'[CURRENCY] ) = 'TABLE'[CURRENCY]
                        && 'TABLE'[PD] >= EARLIER ( 'TABLE'[PD] )
                ),
                'TABLE'[AMOUNT]
            )
        ),
        FILTER ( ALL ( 'TABLE' ), 'TABLE'[VD] > CURRENT_LINE_DATE )
    )
VAR min_date =
    CALCULATE ( MIN ( 'TABLE'[PD] ), ALLEXCEPT ( 'TABLE', 'TABLE'[CURRENCY] ) )
RETURN
    IF ( MAX ( 'TABLE'[PD] ) > min_date, oc, BLANK () )

Sample .pbix 

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

[Greg_Deckler]

@stefansi Sorry, having trouble following, can you post sample data as text and expected output?

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[stefansi]

Thanks very much @Greg_Deckler. I will keep these points in mind for future posts.