Calculate SUM based on date comparison from disconnected table

[Anonymous]

I have the two sample tables below. Table A is the monthly dates. Table B has Date, Category, and Value. My goal is to create a line chart using the dates in Table A and a calculation that sums the Value column in Table B only if the Table A date >= Table B date.

Table A

 

Table B

For example, for January 1 2022, the calculation would exclude row 4 and 6 in Table B. The value would be 5 in the line chart.

In my current model, Table A and B are disconnected. I have tried using Calculate(sum([Value]), 'Table A'[Date] >= 'Table B'[Date]), but I receive an error saying I cannot use columns from multiple tables in a true/false filter. Any help is appreciated!

[parry2k · Accepted solution]

@Anonymous if you want to use disconnected table with your current setup, add following measure, make sure in visual use Date from Table A

Measure 2 = 
VAR __date = MAX ('Table A'[End of Month] )
RETURN
CALCULATE ( SUM ( 'Table B'[Value] ), 'Table B'[Date] < __date ) 

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@Anonymous the answer should be 4 in your example if you remove row 4 and 6, having said that you should have a calendar table with all the dates and relationship with your data table and then use the following measure to get this going

Sum After = CALCULATE ( SUM ('Data'[Value] ), FILTER ( ALL ('Calendar'[Date] ), 'Calendar'[Date] <= MAX ('Calendar'[Date] ) ) )