Forum Discussion
fetching last 60 days data with reference to current system date from oracle database to power bi
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 !
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)))hi Anonymous
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
6 Replies
- amitchandak
Super User
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 LinkedinHi 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.
