Forum Discussion

atasovski's avatar
atasovski
Copper Contributor
Jul 30, 2023

Calculate the sum of turnover on client account by date of loan

Hi all,   Can someone please help, I'm working on a rather big table in PowerQuery(cca 8.000.000 rows) I have a table with 4 columns  Client ID          LoanDate       TurnoverAmount    Turnover...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 31, 2023

atasovski 

With 8 million of rows I'd use PivotTable based on data model. If add the source to data model and use measure

Turnover :=
VAR vLoanDate =
    MIN ( Tabelle1[LoanDate] )
RETURN
    CALCULATE (
        SUM ( Tabelle1[TurnoverAmount] ),
        Tabelle1[TurnoverDate] >= vLoanDate - 30,
        Tabelle1[TurnoverDate] <= vLoanDate
    )

result will be

Resources