Calculate count of items over a rolling window?

[KDev1]

Hi,

I have a main table which has customers for each day for a period of 2 years. 

Example

Date	Customer
1-Jan-24	Customer1
1-Jan-24	Customer2
1-Jan-24	Customer1
2-Jan-24	Customer1
2-Jan-24	Customer3
2-Jan-24	Customer2
3-Jan-24	Customer2
3-Jan-24	Customer4
3-Jan-24	Customer4

A distinct count of customers over each day gives me the below table

Date	count(distinct Customer)
1-Jan-24	2
2-Jan-24	3
3-Jan-24	2

Now, I have to calculate the distinct count of customers for each date over a rolling past 30-day window. 

For example, on 1-Feb-24, I need the distinct count of customers from 1-Jan-24 to 1-Feb-24. On 2-Feb-24, I need the distinct count of customers from 2-Jan-24 to 2-Feb-24. I have tried the below methods so far.

• Using the Rangesum with below function, but it aggregates the distinct count of Customers each day and sums it up over the rolling period.

=rangesum(below(aggr(count(distinct Customer),Date),0,30))

The problem with this is there will be duplicates across the 30 day period and the above formula adds them all up. 

• Hard code the dates like below but it takes the overall table's max date so the count is common across dates.

count(TOTAL DISTINCT{<Date={">=$(=date(max(Date)-30))<=$(=max(Date))"}>} Customer)

• Rangecount doesn't seem to work either. It returns '30' for all dates.

rangecount(below(aggr(count(distinct Customer),date),0,30))

I would really appreciate any help that would help solve this issue.

Thank you very much.

[Vegar · Accepted solution]

You could try to introduce an as of table in your data model. Then you could just do count(distinct Customer)

Consider the image generated from data in the table beneath

LOAD * inline [
    Date,Customer
    2024-04-01,Micromax
    2024-04-01,Panasonic
    2024-04-02,Gigabyte
    2024-04-03,Micromax
    2024-04-04,ZTE
    2024-04-05,Kyocera
    2024-04-05,Motorola
    2024-04-05,Samsung
    2024-04-06,LG
    2024-04-06,Samsung
    2024-04-06,vivo
    2024-04-07,HTC
    2024-04-08,LG
    2024-04-08,Samsung
    2024-04-09,Samsung
    2024-04-10,BLU
    2024-04-11,Asus
    2024-04-13,Lenovo
    2024-04-14,Vodafone
    2024-04-14,ZTE
    2024-04-15,Pantech
    2024-04-15,WND
    2024-04-17,Microsoft
    2024-04-17,Motorola
    2024-04-17,Panasonic
    2024-04-17,Samsung
    2024-04-18,Apple
    2024-04-18,Celkon
    2024-04-18,Sony
    2024-04-19,Motorola
    2024-04-19,Tecno
    2024-04-21,Xiaomi
    2024-04-22,Micromax
    2024-04-22,Mitsubishi
    2024-04-22,Tel.Me.
    2024-04-23,Meizu
    2024-04-24,Sewon
    2024-04-25,LG
    2024-04-26,Philips
    2024-04-26,Samsung
    2024-04-28,Sharp
    2024-04-29,HTC
    2024-04-29,Samsung
    2024-05-01,Samsung
    2024-05-02,Sewon
    2024-05-03,Amoi
    2024-05-03,Gigabyte
    2024-05-03,Sagem
    2024-05-04,BLU
    2024-05-04,LG
    2024-05-04,ZTE
    2024-05-06,HTC
    2024-05-08,alcatel
    2024-05-08,HTC
    2024-05-09,Archos
    2024-05-09,BLU
    2024-05-09,LG
    2024-05-09,Samsung
    2024-05-10,Panasonic
    2024-05-12,Celkon
](delimiter is ',')
;

for each _date in fieldvaluelist('Date') 
LOAD 
	iterno ()-1 as DateDiff,
	'$(_date)' AS Date,
	date('$(_date)' + iterno ()-1)AS AsOfDate
Autogenerate 1
While iterno ()-1 < 6  //6 DAYS, CHANGE TO 30 IF NEEDED
 ;
next

[KDev1]

Hi @Vegar ,

Thank you very much for your reply. This solution works for our scenario. Out of curiosity, is the same possible using measures and set expressions? I really appreciate the help!