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    TurnoverDate

 

I need to calculate the clients total turnover for 30 days counting backwards from the LoanDate.

I've tried so many different approaches but nothing works and I don't know what to do 

 

Can someone please HELP

Thanks in advance  

 

excel

10 Replies

  • 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

    • atasovski's avatar
      atasovski
      Copper Contributor
      Jul 31, 2023

      OliverScheurich it worked perfect but with small number of clients. When I turn off the filter on clients so I can see all clients in the file I got this message

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jul 31, 2023

        atasovski 

        As variant

        let
    getTurnover = Table.Group(
        Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
        {"Client ID", "LoanDate"},
        { {"Turnover",
            each [
                tbl = _,
                loanDate = tbl{0}[LoanDate],
                filter = Table.SelectRows( tbl,
                    (q) => q[TurnoverDate] <= loanDate and
                           q[TurnoverDate] >= Date.AddDays(loanDate, -30)),
                t = List.Sum( filter[TurnoverAmount] )
            ][t], Currency.Type
        }  } )
in
    getTurnover

Resources