cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
STF29
Regular Visitor

cumulative sum based on a variable data range

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

 

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

@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,

Screenshot 2021-03-13 175055.png

Screenshot 2021-03-13 175340.png

View solution in original post

4 REPLIES 4
CNENFRNL
Community Champion
Community Champion

@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,

Screenshot 2021-03-13 175055.png

Screenshot 2021-03-13 175340.png

View solution in original post

STF29
Regular Visitor

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
Community Champion
Community Champion

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.

Screenshot 2021-03-14 050429.png

STF29
Regular Visitor

@CNENFRNL , thank you! Solved!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!