Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cumulative sum between a date range from two different date ranges

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/20175
2/1/201710
2/1/201850
2/1/2019500

 

here is my second table

 

Asset IDInstall 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 IDInstall DateWhat I want

asset 1

1/1/2017565

asset 2

1/1/2018550

asset 3

1/1/2019500

asset 4

1/1/20200

asset 5

1/1/2017565

 

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

cumulative
multiple date tables
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    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

     

     

3 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    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