cumulative sum based on a variable data range

[STF29]

Hi Everyone,

I need a dax function that provides a cumulative sum based on a variable range of dates.

I have a table with details of the Contract:

N contract	Customer	Start date	End date
#1	AAA	01/01/2020	29/02/2020
#2	AAA	01/03/2020	30/06/2020

And a table of Deliveries:

customer	date of delivery	quantity
AAA	02/01/2020	1
AAA	15/02/2020	3
AAA	10/03/2020	2
AAA	05/04/2020	3
AAA	29/05/2020	1

I need to monitor the cumulative sum of quantity delivered, for each month, referring to the duration of the contract.

Example of expected result:

Month	q delivered	cumulative sum
Jen	1	1
Feb	3	4
Mar	2	2
Apr	3	5
May	1	6
Jun	0	6

Can anyone please help?

Thanks

[CNENFRNL · Accepted solution]

@STF29 , your question is interesting and here's a tricky solution.

Convert your contract table to the following form for the purpose of filtering in DAX,

[STF29]

Hi @CNENFRNL, thank you for your quick support!

Could you please suggest a way to convert the contract table to the format that you proposed?

Many thanks

[CNENFRNL]

Hi, @STF29 , in the attachment of my preceding reply, you might want to go through all applied steps of convesion in detail in Power Query.

[STF29]

@CNENFRNL , thank you! Solved!