How can a certain query take a specific date from another query?

Question

I have a certain query with lots of past and future dates, also I have added columns in it. Let's call it query number 1

My goal is that query number 1 will take a specific date from another query, which is called query number 2,this date must be yesterday's date. So I created a formula in query number 2 that would get me the date directly.

My question is how do I make the dates in query number 1 filter according to the specific date I put in query number 2?&nbsp;

bhanu_gautam · Answer

Kacol&nbsp;, Create a new query 2&nbsp;
&nbsp;

&nbsp; let
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Yesterday = Date.From(DateTime.LocalNow()) - #duration(1, 0, 0, 0)
&nbsp; &nbsp; &nbsp;in
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Yesterday
&nbsp;
This will give yesterday's date
&nbsp;
Create a relation between Query 1 and Query 2
&nbsp;
Create a measure in Query 1 to filter on the basis of Query 2
&nbsp;
FilteredData = CALCULATE(COUNTROWS(Query1),Query1[DateColumn] = VALUES(Query2[Yesterday]))
&nbsp;
And use this measure in visuals

kishore_kvn · Answer

Hello&nbsp;Kacol&nbsp;,
Don't create query2 with yesterdays date. Instead of that create a parameter in Power Query Editor for yesterday's date and use that parameter to filter this table.&nbsp;
Please refer below video for details:
Power Query Parameters - Dynamically Change the Output of Your Queries Depending on Their Value - YouTubeIf this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

kacol · Answer

Thanks for trying to help, but it still didn't solve my problem. I can't figure out how I still filter query number 1, what I saw in the video is just a parameter in the form of text.I'm still not sure how to automatically filter by yesterday's date.

kacol · Answer

Thanks for the answer, but my problem is even more complex than I thought.&nbsp;I simply import data through external software, some of the tables include time, financial data, stocks, etc. Only from the moment I import the "time column" can I continue to import additional columns. The dates in "Time" are not defined as a date but as text. Once I change it from text to date format, I can't go ahead and import more data. That's why it's complicated. Is there some way to still filter the time so that it gives me yesterday's automatic date? The dates that appear are in the past and in the future.&nbsp;

Forum Discussion

Related content

Specific matrix

Measure to SUM if column contains specific text or another column contains specific text

Saving Notebooks to specific folder

How to search specific word in table filter Calculations

Drill through excluding specific record