fetching last 60 days data with reference to current system date from oracle database to power bi

[tarun912]

Hi everyone, 

Can somebody please tell me, how to fetch last 60 days data with reference to current system date from oracle database to power bi desktop.
I tried changing the SQL script in the get data option, but getting this error ("The keys didn't match any rows in the table").

SQL SCRIPT : 
SELECT * FROM TABLENAME
WHERE COLUMN_NAME >= TRUNC(sysdate)-60


Hope to hear from you soon.

Thanks !

[danextian · Accepted solution]

Have you tried filtering your table from the Query Editor instead of directly through SQL? 

Filter the date colum to any date and then in the formula bar replace the filter condition similar to below:

Table.SelectRows(#"Removed Columns", each ([Transaction Date] = #date(2018, 1, 1)))

Table.SelectRows(#"Removed Columns", each ([Transaction Date] >=Date.AddDays(Date.From(DateTime.LocalNow()),-60)))

[amitchandak]

If you have date table. you can get easily like

Rolling last 60 days = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],today(),-60,Day))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[tarun912]

Hi Amit, 

Thanks for providing me with the query.
I understand that query, but my issue is :
- I have uploaded entire dataset from oracle database, but when I am publishing my report to the power bi service and then schedule refresh the dataset there , it is giving me a timeout error because schedule refresh in power bi doesn't support of dataset load time of more than 2 hrs and in my case dataset is taking around 5.5 hrs in loading (I noted this time when I refreshed the dataset in power bi desktop) 

So, I just want to pull only 200 days back data via Get data option so that I will have less data in power bi desktop (only the required dataset) and therefore power bi service refresh will work successfully.

And if you know any other way of getting this thing then please let me know.
I think now you will got my point. 
Hope to hear from you soon.
Thanks !

[amitchandak]

While creating a connection , you have the option of advance, then you can write a query in that, do this for the transaction table.

[tarun912]

ya sure, I did that 
I used this SQL script :
SELECT * FROM TABLE_NAME
WHERE COLUMN_NAME >= TRUNC(sysdate)-194


but getting the following error "THE KEY DIDN'T MATCH ANY ROWS IN THE TABLE".

[v-lili6-msft · Accepted solution]

hi  @tarun912 

I would suggest you filter it in edit queries as below:

Step1:

Get all data without "where" conditional in code.

SELECT * FROM TABLE_NAME

Step2:

In edit queries, add a custom column as below

Duration.Days (DateTime.Date(DateTime.LocalNow()) - [COLUMN_NAME] )

Step3:

Filter this custom column that is less than 194

Result:

and you could remove this custom column.

here is my sample file, please try it.

Regards,

Lin

[danextian · Accepted solution]

Have you tried filtering your table from the Query Editor instead of directly through SQL? 

Filter the date colum to any date and then in the formula bar replace the filter condition similar to below:

Table.SelectRows(#"Removed Columns", each ([Transaction Date] = #date(2018, 1, 1)))

Table.SelectRows(#"Removed Columns", each ([Transaction Date] >=Date.AddDays(Date.From(DateTime.LocalNow()),-60)))