Forum Discussion
Calculate SUM based on date comparison from disconnected table
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!
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.⚡
2 Replies
- parry2k
Super User
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] ) ) ) - parry2k
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.⚡