cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: Syncing Dates Across Multiple Tables

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

 


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Highlighted
Super User IX
Super User IX

Re: Syncing Dates Across Multiple Tables

Sounds like you need to do something like:

 

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

---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper II
Helper II

Re: Syncing Dates Across Multiple Tables

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.

Highlighted
Super User IX
Super User IX

Re: Syncing Dates Across Multiple Tables

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

 


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Helper II
Helper II

Re: Syncing Dates Across Multiple Tables

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!

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors