Forum Discussion

Kacol's avatar
Kacol
Regular Visitor
2 years ago

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

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...
General Comment
Need Help
bhanu_gautam's avatar
bhanu_gautam
Super User
2 years ago

Kacol , Create a new query 2 

 

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

Thanks for the answer, but my problem is even more complex than I thought. 

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.