Create cumulative measure with several filters

[Anonymous]

Hi everyone !

I need your help to create a measure : 

Here is my raw data : 

RAW Data

Payment date	Delivery Date	# Waiting days	Unity Sold	Product	Colour
2023-05-09	2023-05-08	-1	4	Car	Red
2023-05-08	2023-05-08	0	918	Chair	Blue
2023-05-07	2023-05-08	1	584	Pen	Orange
2023-05-06	2023-05-08	2	169	House	Yellow
2023-05-05	2023-05-08	3	441	Table	Purple
2023-05-04	2023-05-08	4	291	Car	Red
2023-05-03	2023-05-08	5	364	Chair	Red
2023-05-02	2023-05-08	6	348	Pen	Blue
2023-05-01	2023-05-08	7	330	House	Green
2023-04-30	2023-05-08	8	110	Table	Blue
2023-04-29	2023-05-08	9	70	Car	Orange
2023-04-28	2023-05-08	10	201	Chair	Red
2023-04-27	2023-05-08	11	591	Car	Orange
2023-04-26	2023-05-08	12	243	Car	Blue
2023-04-25	2023-05-08	13	167	Chair	Red
2023-04-23	2023-05-08	15	426	Pen	Red
2023-04-21	2023-05-08	17	121	Car	Blue
2023-04-19	2023-05-08	19	321	Chair	Orange
2023-04-17	2023-05-08	21	189	Pen	Yellow
2023-04-14	2023-05-08	24	142	House	Purple
2023-04-10	2023-05-08	28	221	Table	Red
2023-04-03	2023-05-08	35	245	Car	Red
2023-03-27	2023-05-08	42	373	Chair	Blue
2023-03-20	2023-05-08	49	255	Pen	Green
2023-03-13	2023-05-08	56	208	House	Blue
2023-03-06	2023-05-08	63	154	Table	Orange
2023-02-20	2023-05-08	77	190	Car	Red
2023-02-06	2023-05-08	91	193	House	Orange
2023-01-09	2023-05-08	119	54	Table	Blue
2022-12-12	2023-05-08	147	6	Car	Red
2022-11-14	2023-05-08	175	7	House	Red
2022-10-17	2023-05-08	203	3	Table	Blue
2022-08-22	2023-05-08	259	4	Car	Orange
2023-05-10	2023-05-09	-1	4	Table	Yellow
2023-05-09	2023-05-09	0	1288	Car	Purple
2023-05-08	2023-05-09	1	1128	Chair	Red
2023-05-07	2023-05-09	2	253	Pen	Red
2023-05-06	2023-05-09	3	121	House	Blue
2023-05-05	2023-05-09	4	502	Table	Green
2023-05-04	2023-05-09	5	374	Car	Blue
2023-05-03	2023-05-09	6	473	House	Orange
2023-05-02	2023-05-09	7	338	Table	Red
2023-05-01	2023-05-09	8	509	Car	Orange
2023-04-30	2023-05-09	9	56	House	Blue
2023-04-29	2023-05-09	10	90	Table	Red
2023-04-28	2023-05-09	11	157	Car	Red
2023-04-27	2023-05-09	12	620	Table	Blue
2023-04-26	2023-05-09	13	149	Car	Orange
2023-04-24	2023-05-09	15	600	Chair	Yellow
2023-04-22	2023-05-09	17	88	Pen	Purple
2023-04-20	2023-05-09	19	307	House	Red
2023-04-18	2023-05-09	21	134	Table	Red
2023-04-15	2023-05-09	24	105	Car	Blue
2023-04-11	2023-05-09	28	193	House	Green
2023-04-04	2023-05-09	35	305	Table	Blue
2023-03-28	2023-05-09	42	221	Car	Orange
2023-03-21	2023-05-09	49	192	House	Red
2023-03-14	2023-05-09	56	122	Table	Orange
2023-03-07	2023-05-09	63	105	Car	Blue
2023-02-21	2023-05-09	77	112	Table	Red
2023-02-07	2023-05-09	91	234	Car	Red
2023-01-10	2023-05-09	119	36	Chair	Blue
2022-12-13	2023-05-09	147	11	Pen	Orange
2022-10-18	2023-05-09	203	4	House	Yellow
2022-08-23	2023-05-09	259	4	Table	Purple
2023-05-10	2023-05-10	0	1472	Car	Red
2023-05-09	2023-05-10	1	974	House	Red
2023-05-08	2023-05-10	2	906	Table	Blue
2023-05-07	2023-05-10	3	190	Car	Green
2023-05-06	2023-05-10	4	121	House	Blue
2023-05-05	2023-05-10	5	434	Table	Orange
2023-05-04	2023-05-10	6	370	Car	Red
2023-05-03	2023-05-10	7	357	Table	Orange
2023-05-02	2023-05-10	8	334	Car	Blue
2023-05-01	2023-05-10	9	379	Chair	Red
2023-04-30	2023-05-10	10	95	Pen	Red
2023-04-29	2023-05-10	11	45	House	Blue
2023-04-28	2023-05-10	12	228	Table	Orange
2023-04-27	2023-05-10	13	603	Car	Yellow
2023-04-25	2023-05-10	15	585	House	Purple
2023-04-23	2023-05-10	17	192	Table	Red
2023-04-21	2023-05-10	19	290	Car	Red
2023-04-19	2023-05-10	21	204	House	Blue
2023-04-16	2023-05-10	24	250	Table	Green
2023-04-12	2023-05-10	28	187	Car	Blue
2023-04-05	2023-05-10	35	290	Table	Orange
2023-03-29	2023-05-10	42	220	Car	Red
2023-03-22	2023-05-10	49	265	Chair	Orange
2023-03-15	2023-05-10	56	139	Pen	Blue
2023-03-08	2023-05-10	63	142	House	Red
2023-02-22	2023-05-10	77	158	Table	Red
2023-02-08	2023-05-10	91	166	Car	Blue
2023-01-11	2023-05-10	119	23	House	Orange
2022-12-14	2023-05-10	147	11	Table	Yellow
2022-11-16	2023-05-10	175	3	Car	Purple
2022-10-19	2023-05-10	203	1	House	Red
2022-09-21	2023-05-10	231	1	Table	Red

What I need to do is to create a measure that would allow me for every #waiting days to see the sum of sales inferior or equal to this #waiting days, that were paid on or after 2023-04-05, and only for delivery date for which there could have been sales at the specified #waiting days

Here is a view based on the above data with a measure calculating only sales paid on or after 2023-04-05 :

Delivery Date	# Waiting days	Sales filtered
2023-05-08	-1	4
2023-05-08	0	918
2023-05-08	1	584
2023-05-08	2	169
2023-05-08	3	441
2023-05-08	4	291
2023-05-08	5	364
2023-05-08	6	348
2023-05-08	7	330
2023-05-08	8	110
2023-05-08	9	70
2023-05-08	10	201
2023-05-08	11	591
2023-05-08	12	243
2023-05-08	13	167
2023-05-08	15	426
2023-05-08	17	121
2023-05-08	19	321
2023-05-08	21	189
2023-05-08	24	142
2023-05-08	28	221
2023-05-09	-1	4
2023-05-09	0	1288
2023-05-09	1	1128
2023-05-09	2	253
2023-05-09	3	121
2023-05-09	4	502
2023-05-09	5	374
2023-05-09	6	473
2023-05-09	7	338
2023-05-09	8	509
2023-05-09	9	56
2023-05-09	10	90
2023-05-09	11	157
2023-05-09	12	620
2023-05-09	13	149
2023-05-09	15	600
2023-05-09	17	88
2023-05-09	19	307
2023-05-09	21	134
2023-05-09	24	105
2023-05-09	28	193
2023-05-10	0	1472
2023-05-10	1	974
2023-05-10	2	906
2023-05-10	3	190
2023-05-10	4	121
2023-05-10	5	434
2023-05-10	6	370
2023-05-10	7	357
2023-05-10	8	334
2023-05-10	9	379
2023-05-10	10	95
2023-05-10	11	45
2023-05-10	12	228
2023-05-10	13	603
2023-05-10	15	585
2023-05-10	17	192
2023-05-10	19	290
2023-05-10	21	204
2023-05-10	24	250
2023-05-10	28	187
2023-05-10	35	290

My expected result is : 

# Waiting days	Expected Measure
-1	8
0	3686
1	6372
2	7700
3	8452
4	9366
5	10538
6	11729
7	12757
8	13707
9	14212
10	12598
11	15391
12	16482
13	17401
15	19012
17	19413
19	20331
21	20858
24	21355
28	21956
35	8506

The way the measure should work is for every # waiting days :

• Sum the sales that have an equal or inferior number of # waiting days (For instance at #waiting days 19, I need to see all the sales for which the number of waiting days is 19 or less)
• Only taking into account sales that were paid on or after 2023-04-05
• But also only taking into account delivery dates on which delivery at this specific #waiting days were possible with payment on or after 2023-04-05. For instance, when I look at #waiting days 35, I only want sales with delivery date on or after 2023-05-10 (there are 35 days between 2023-04-05 and 2023-05-10, When I look at #waiting days 42, I only want sales with delivery date on or after 2023-05-17. That is why in the previous example, #waiting days 35 only includes delivery made on 2023-05-10 (it is the only delivery date in the example on or after 2023-05-10).

I am completely stuck here,

Thank you in advance !

[v-rzhou-msft]

Hi @Anonymous ,

I suggest you to try code as below to create a measure.

Running Total = 
VAR _SUM1 =
    CALCULATE (
        SUM ( 'Table'[Unity Sold] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Payment date] >= DATE ( 2023, 04, 05 )
                && 'Table'[# Waiting days] <= MAX ( 'Table'[# Waiting days] )
        )
    )
VAR _SUM2 =
    CALCULATE (
        SUM ( 'Table'[Unity Sold] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Payment date] >= DATE ( 2023, 04, 05 )
                && 'Table'[# Waiting days] <= MAX ( 'Table'[# Waiting days] )
                && 'Table'[Delivery Date]
                    = DATE ( 2023, 04, 05 ) + 35
        )
    )
RETURN
    IF (
        MAX ( 'Table'[# Waiting days] ) <= 35,
        IF ( MAX ( 'Table'[# Waiting days] ) < 35, _SUM1, _SUM2 )
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello  ! Thank you for your answer ! 🙂

It is helping but the limitation on number of dates being taken into account only works for #waiting days 35, I was just wondering what could be change for this to work not only on #waiting days 35, but for all #waiting days ?

• When I look at #waiting days 35, I only want sales with delivery on or after 2023-05-10 (there are 35 days between 2023-04-05 and 2023-05-10)
• When I look at #waiting days 42, I only want sales with delivery on or after 2023-05-17 (there are 42 days between 2023-04-05 and 2023-05-17)
• etc.

I have modified the initial post so it could be clearer.

For instance, if I add theses ligns to the initial data:

2023-04-18	2023-05-30	42	100	Table	Green
2023-05-30	2023-05-30	1	1	Table	Green

The expected result would be :

# Waiting days Expected Measure

-1	8
0	3687
1	6373
2	7701
3	8453
4	9367
5	10539
6	11730
7	12758
8	13708
9	14213
10	12599
11	15392
12	16483
13	17402
15	19013
17	19414
19	20332
21	20859
24	21356
28	21957
35	8507
42	101

Thank you !