Data sets for 10 weeks of data.

[amtrakrob]

We have business analysts that move from project to project.  Some of these moves are done on a weekly basis.
So...

I have a report that refreshes Every Monday telling managers what project the BAs are assigned.  Now I've been tasked with showing a 10-week history for each BA.  I'm not sure the best way to do this, I'm looking for ideas.

Is it possible to roll the data from data set to data set each week.  So on each Monday, before updating the current data set, the current data would move to the week-old data set, the week-old data set would move to the two-week-old data set, the two-week-old data set would move to the three-week-old data set (and so on…)

Or if anyone has another idea, I'm open to it.

BTW.. The data source is an Oracle Database.  Creating another Oracle table to track this changes as already been shot down.

Thanks!

[v-stephen-msft]

Hi @amtrakrob ,

It sounds like you need incremental refresh. 

Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Docs

Configure incremental refresh and real-time data for Power BI datasets - Power BI | Microsoft Docs

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tackytechtom]

Hi @amtrakrob ,

I hope I understood you correctly: You would like to have a "rolling" 10 week window on your data right? How about creating a view in the Oracle database with a WHERE clause close to this:

WHERE Date >= DATEADD ( week, -10, GetDate() )

Alternatively, you could directly use a sql query with such a dynamic filter in Power Query.

Would this work? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/