topic Re: Cumulative sum between a date range from two different date ranges in DAX Commands and Tips

Cumulative sum between a date range from two different date ranges

Anonymous — Sun, 24 Nov 2019 06:53:51 GMT

Hello everyone,

This is my first time using the forum so hopefully I have disobeyed any rules, apologises in advance.

I am trying to sum an amount from one table based upon a date from another table. The amount can't be totalled if the production date is in the past of the install date.

Here is my first Table.

Production Table

Production date	Amount
2/1/2017	5
2/1/2017	10
2/1/2018	50
2/1/2019	500

here is my second table

Asset ID	Install Date
asset 1	1/1/2017
asset 2	1/1/2018
asset 3	1/1/2019
asset 4	1/1/2020
asset 5	1/1/2017

So I would expect my results as this.

Asset ID	Install Date	What I want
asset 1	1/1/2017	565
asset 2	1/1/2018	550
asset 3	1/1/2019	500
asset 4	1/1/2020	0
asset 5	1/1/2017	565

I have a slicer table that has a unique date range.

I was trying = calculate (sum(table1[amount])), filter(allselected(table1), production date > userrelationship(production date, install date)), but that doesn't seem to work.

Any ideas would be very appreciated.

Thanks in advance

Re: Cumulative sum between a date range from two different date ranges

Nathaniel_C — Mon, 25 Nov 2019 01:00:39 GMT

Hi Anonymous ,
Try this :

Sum of Amounts = SUMX(Production,IF(Production[Production date]>MAX(mySecond[Install Date]),Production[Amount],0))

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Re: Cumulative sum between a date range from two different date ranges

Anonymous — Mon, 25 Nov 2019 01:31:25 GMT

LEGEND!!!!! KUDOS Nathaniel_C

Re: Cumulative sum between a date range from two different date ranges

Nathaniel_C — Mon, 25 Nov 2019 01:32:24 GMT

Anonymous , you are welcome!