Forum Discussion
Syncing Dates Across Multiple Tables
Hello,
I am trying to figure out how to solve filtering across two different tables by date. My first table has financial forecasts. There's a projection date field and generally speaking, there's a forecast for each project made once or twice a month (could be more often). The other table is a transaction table with numerous transactions spanning multiple months/years. What I am trying to do is to have the user select a forecast date and then see all of the accumulated transactions as of that date. So if I forecast $1,000 on the first of January 2018, I need to compare that forecast to all of the transaction dollars accumulated up until that point. My one thought was to create a summary table based off of the forecast date. Any suggestions would be appreciated.
Thanks.
You could wrap your [Project ID] columns in a MAX potentially. Sample data would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
4 Replies
Sounds like you need to do something like:
Measure = CALCULATE(SUM(Table2[Column]),FILTER(ALL(Table2),Table2[Date]<=MAX(Table1[Date])))
Greg, definitely on the right track. However, I need to also have a connection to each particular project. I tried adding another FILTER(ALL(Table2, Table2[ProjectID = Table1[ProjectID)), but that returns an error. It's looking for some type of aggregation.
Thanks again.
You could wrap your [Project ID] columns in a MAX potentially. Sample data would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
