The data in our e-commerce database updates every thirty minutes. Our dataset is using direct query to ensure our reporting has the most up-to-date information. We are on a Pro license, so we get 8 scheduled refreshes per day.
I've been using Power BI for a number of years, but this is the first time I've tried to use one model to cover both historical sales and current day's selling. Previously they were two separate data sets, one was a refresh each morning, and one was a separate direct query model that pulled only today's information. These models are basically reporting the same data.
To speed up report performance, I set up aggregations and it worked very well. However, the problem is that our "Today" report is only picking up the data as of the latest update, not the live data through direct query.
So, for example, if the last full refresh was at noon, when the "Today" report is opened at 3, the data is only showing as of noon, not including what has happened since the last refresh.
Is there a way to setup my dataset / aggregations to force the current date to use direct query, but have all of the historical data use the aggregation tables I've created? If I have the aggregation tables only include data before today will that force the model to pull the latest data for today?
I would appreciate any help/advice offered! Thank you.
