Caluclate COUNT with grouping and dynamic date range

[lnz]

Hi, guys!

I hope you can help me with following problem:

I have a table Sales with next structure:

[Column] - Description

-----------------------
[SaleID] - ID of sale
[SaleDate] - Date of sale
[ContractID] - ID of contract. On one contract may be sereval sales
[ContractDate] - Date when contract was signed
[ClientName] - Cleint Name. One cleint can have several contracrats
[Maneger] - Manger Name. One manager can have several clients. Eeach cleint correspond only to one manager
[HasNewContractInPeriod] - The flag that informs of the fact of concluding the new contract for this client, under which took place at least one sale in a period of 90 days before the date of current sale.

The problem is to calulate the [HasNewContractInPeriod] column.

I suppose it should be something like (that just a prototype of query):

SELECT ContractID, COUNT(SaleID) .... FROM Sales WHERE ContractDate BETWEEN ((SaleDate - 90, SaleDate) AND GroupBY ClientName HAVING COUNT(SaleID) > 1

[v-shex-msft]

Hi @lnz,

You can use below formula to achieve your goal.

Calculate column:

HasNewContractInPeriod = if(COUNTAX(FILTER(ALL(Sheet1),Sheet1[ContractID]=EARLIER(Sheet1[ContractID])&&Sheet1[ContractDate]>=DATEADD(Sheet1[SaleDate],-90,DAY)&&Sheet1[ContractDate]<=Sheet1[SaleDate]),[SaleID])>0,TRUE(),FALSE())

Regards,

Xiaoxin Sheng

[lnz · Accepted solution]

@v-shex-msft, thanks for reply!

I got your idea, but it's not my way.

Finaly I decide to solve this task with SQL. Here is my code:

UPDATE "Sales" AS s1 
SET "maxClientContractDate" = (SELECT "mDate"
	FROM (SELECT "ContractID", MAX("ContractDate") AS "mDate"
		FROM "sales" 
		WHERE "SaleDate" < s1."SaleDate"
		AND "ClientName" =  s1."ClientName" 
		GROUP BY "ContractID" 
		HAVING COUNT(*) > 0) AS cs
	WHERE
        "mDate" < p1."SaleDate",
        ORDER BY "mDate" DESC LIMIT 1;

UPDATE "Sales" AS s1
SET "hasNewContractInPeriod" = 'TRUE' 
     WHERE "SaleDate" - "maxClientContractDate" > 90;