How to create Sumifs in Date table with Dax

[Phunt]

I have a data of trading like A1:G3 with the trade volume from date to date. Now i want to get the outstanding balance at the end of each day between that time. In excel i use Sumifs. Expected results as in A6:B11. Can you help to create in PBI?

[v-zhengdxu-msft]

Hi @Phunt 

Please try this:

First of all, I create a sample table:

Then add a new table:

Table 2 =
VAR _newtable =
    FILTER (
        CROSSJOIN ( CALENDAR ( "2024-1-1", "2024-1-5" ), 'Table' ),
        [Date] < MAX ( 'Table'[Value Date] )
            && 'Table'[Value Date] <> MAX ( 'Table'[Value Date] )
            && 'Table'[Mat Date] > [Date]
            || [Date] >= MAX ( 'Table'[Value Date] )
                && 'Table'[Value Date] = MAX ( 'Table'[Value Date] )
                && 'Table'[Mat Date] > [Date]
    )
RETURN
    SELECTCOLUMNS (
        _newtable,
        [Date],
        "Outstanding Amt",
            IF (
                [Date] >= MAX ( 'Table'[Value Date] ),
                MAX ( 'Table'[Amt] ) + MIN ( 'Table'[Amt] ),
                'Table'[Amt]
            )
    )

 The result is as follow:

Best Regards

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

[Phunt]

Thank you @ Zhendong Xu. but my actual data having thousands line (trade date - its may be years) so cannot using like this, right?

I have a Date Table, i would like to add a columm on this Date Table and using Dax on the colum, but its not workable. Can you help