Running Total Through Direct Query

[Anonymous]

I need to calculate the running total for a tabular data. I need to use direct query mode.

There are Filters(Slicers) for Date and Product in the report. When we filter the data based on Date and Product, the running total should be calculated as per the filtered applied.

Date	Product	Cost	Running Total
1/1/2019	A	10	10
1/1/2019	B	20	30
1/1/2019	C	25	55
1/2/2019	A	45	100
1/2/2019	C	65	165
1/3/2019	B	15	180
1/3/2019	A	20	200

Need help in achieving data like above.

[v-chuncz-msft · Accepted solution]

@Anonymous ,

You may refer to the measure below.

Measure =
CALCULATE (
    SUM ( 'Table1'[Cost] ),
    FILTER (
        SUMMARIZE ( ALLSELECTED ( 'Table1' ), 'Table1'[Date], 'Table1'[Product] ),
        ISONORAFTER (
                'Table1'[Date], MAX ( 'Table1'[Date] ), DESC,
                'Table1'[Product], MAX ( 'Table1'[Product] ), DESC
        )
    )
)

[Anonymous]

Hi @v-chuncz-msft ,

Thank you for your help. 🙂

I was able to achieve the required runnning total using the Measure formulae you shared.

Thanks,

Ravin