Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

@CNENFRNL , thank you! Solved!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors