Syncing Dates Across Multiple Tables

[tlenzmeier]

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.

[Greg_Deckler]

Sounds like you need to do something like:

Measure = CALCULATE(SUM(Table2[Column]),FILTER(ALL(Table2),Table2[Date]<=MAX(Table1[Date])))

[tlenzmeier]

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.

[Greg_Deckler · Accepted solution]

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

[tlenzmeier]

Greg,

I am still having issues. I have a link to OneDrive where I have my PBIX file. https://adolfsonpeterson-my.sharepoint.com/:u:/g/personal/tlenzmeier_a-p_com/EVFfBpTdhGdMr8cs76X64hs...

To summarize, for each projection date in the projection history table, I need to amount to date costs from the project transactions table.

Thanks, in advance!