Create dynamic measure to sum the value in one table column if matches value in another table column

[92502014]

Hello Everyone, 

I have two tables:

Table 1                                             

ID         Date

3311     10/31/2020

3312      1/5/2021

3313      12/8/2020

Table 2

ID          Date                 Revenue      Product

3311       6/1/2020           50                 A

3311      11/30/2021        100               A

3311       2/20/2020         300               B

3311       1/10/2021         200               C

How can I create two dynamic measure, before value & after value that will return the revenue if the date in table 2 is before/after the date in table 1?

e.g. 3311 before value will be 50+300 = 350; after value will be 100+200 = 300 (but when I use this meausre, it can still show revenue by product)

Thanks!

[VahidDM · Accepted solution]

Hi @92502014 

Try these measures:

After value = 
CALCULATE(sum('Table 2'[Revenue]),filter('Table 2','Table 2'[Date]>max('Table 1'[Date])))

before value = 
CALCULATE(sum('Table 2'[Revenue]),filter('Table 2','Table 2'[Date]<max('Table 1'[Date])))

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[92502014 · Accepted solution]

Thanks! I should include more value in the table 2, my bad! Is there a way to match the ID as well?

In fact, I also have multiple IDs in table 2, so I need to match the ID as well.

Table 1                                             

ID         Date

3311     10/31/2020

3312      1/5/2021

3313      12/8/2020

Table 2

ID          Date                 Revenue      Product

3311       6/1/2020           50                 A

3311      11/30/2021        100               A

3311       2/20/2020         300               B

3311       1/10/2021         200               C

3312       3/7/2020           10                 A

3313       8/18/2020         100               C

[VahidDM · Accepted solution]

Hi @92502014 

Try these measures:

After value = 
CALCULATE(sum('Table 2'[Revenue]),filter('Table 2','Table 2'[Date]>max('Table 1'[Date])))

before value = 
CALCULATE(sum('Table 2'[Revenue]),filter('Table 2','Table 2'[Date]<max('Table 1'[Date])))

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[92502014 · Accepted solution]

Thanks! I should include more value in the table 2, my bad! Is there a way to match the ID as well?

In fact, I also have multiple IDs in table 2, so I need to match the ID as well.

Table 1                                             

ID         Date

3311     10/31/2020

3312      1/5/2021

3313      12/8/2020

Table 2

ID          Date                 Revenue      Product

3311       6/1/2020           50                 A

3311      11/30/2021        100               A

3311       2/20/2020         300               B

3311       1/10/2021         200               C

3312       3/7/2020           10                 A

3313       8/18/2020         100               C

[VahidDM · Accepted solution]

@92502014 

Connect your tables (Relationship) with the ID columns, then test my codes, I think those codes would work well.

Let me know if you ran into any problem.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/