How to Calculate 30-Day Client Turnover Before a Loan Date in WPS Spreadsheets
Question details
The user needs to calculate each client's total turnover during the 30-day period ending on that client's LoanDate from a large Power Query dataset, using a Data Model DAX measure instead of processing millions of rows directly in Power Query.

- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Analyzing a large Power Query dataset with millions of turnover rows and a client LoanDate; the goal is to return a 30-day pre-loan turnover value for each client.
- Observed behavior
- Processing millions of rows directly in Power Query is inefficient, so the calculation must be performed by a DAX measure in the Data Model.
Make sure the turnover table is loaded into the WPS Spreadsheets Data Model, and verify that TurnoverDate and LoanDate are recognized as date columns so the DAX time filter works correctly.
Create a PivotTable from the Data Model and add a DAX turnover measure
Use this method for large datasets because it keeps the calculation in the Data Model and avoids expanding millions of rows in Power Query.
This DAX measure returns each client's total TurnoverAmount where TurnoverDate is between LoanDate-30 and LoanDate inclusive. Replace the table and column names with the exact names in your model.
In WPS Spreadsheets, open the workbook and load the turnover table through Data > Get Data or add the source table to the Data Model, making sure the table is available for PivotTable reporting.
Click Insert > PivotTable, choose the Data Model/external data source option, select the table that contains TurnoverDate, TurnoverAmount, LoanDate, and the client identifier, then place the PivotTable on a new worksheet.
Open Power Pivot > Measures > New Measure or the equivalent Data Model measure option, and enter: Turnover := VAR vLoanDate = MIN(Tabelle1[LoanDate]) RETURN CALCULATE(SUM(Tabelle1[TurnoverAmount]), Tabelle1[TurnoverDate] >= vLoanDate - 30, Tabelle1[TurnoverDate] <= vLoanDate).
Drag the client identifier field into Rows and the new Turnover measure into Values. The PivotTable now shows each client's 30-day turnover ending on their LoanDate.

Calculate 30-Day Client Turnover Before a Loan Date with WPS Spreadsheets
Use WPS Spreadsheets to create a PivotTable from the Data Model and add a DAX measure that filters TurnoverDate between LoanDate-30 and LoanDate. This returns each client's 30-day turnover without expanding the full Power Query dataset.
- 1. Open WPS Spreadsheets and load the workbook: Launch WPS Spreadsheets, open the workbook containing the Power Query output or source data, and save it as a WPS-compatible spreadsheet if needed.
- 2. Add the turnover table to the Data Model: Use Data > Get Data or the PivotTable Data Model option to import the turnover table and ensure TurnoverDate, TurnoverAmount, LoanDate, and client ID are available in the model.
- 3. Insert a PivotTable from the Data Model: Click Insert > PivotTable, select Data Model, choose the loaded table, and place the PivotTable on a new worksheet.
- 4. Create the DAX turnover measure: Open the Data Model measure editor, select New Measure, and enter the DAX formula with your actual table and column names.
- 5. Display client 30-day turnover: Add the client identifier to Rows and the Turnover measure to Values to show the 30-day turnover ending on each client's LoanDate.

Frequently Asked Questions
Why use a DAX measure instead of processing 8 million rows in Power Query?
A DAX measure calculates in the Data Model and only evaluates values for the PivotTable cells. Power Query would have to expand and join millions of rows, which is much slower and more memory-intensive.
What does the condition TurnoverDate >= vLoanDate - 30 do?
It filters turnover dates to the 30-day window ending on the client's LoanDate. vLoanDate is obtained from the current client context by MIN(Tabelle1[LoanDate]), and the filter includes the LoanDate itself.
How do I replace the table and column names in the measure?
Edit the measure and replace Tabelle1, LoanDate, TurnoverDate, and TurnoverAmount with the exact table and column names shown in your Data Model or Power Pivot field list.
Can this measure work if LoanDate or TurnoverDate is stored as text?
No, DAX date comparisons require real date or datetime columns. Change the column type to Date or DateTime in Power Query or the Data Model before creating the measure.
How do I add the source table to the Data Model in WPS Spreadsheets?
Use Data > Get Data or the PivotTable dialog and enable the Data Model option when importing. Then confirm the table appears in the Data Model field list before writing the measure.




