How would you tackle the following scenario?

soptim_vond · ‎03-04-2020

Hi folks,

I am currently trying to figure out how to achieve the following:

Let´s say I have a Power BI Dataset that is automatically refreshed on a daily basis using the enterprise gateway. There is no incremental refresh going on since we don´t have power bi premium. Now, I would like to historicize the dataset once a month, so that I can monitor how the results change over time.

I could also live with a solution that merely leaves me with monthly copies of the dataset, but the desired outcome would be the following:

On the one hand I have the primary dataset that has the newest data (daily refresh) and next to that I have a dataset that contains all monthly copies of the data with a timestamp of the refresh.

A third option would be to not historicize the full dataset, but only certain key results from the reports.

Do you have any ideas on how to achieve any of the outcomes described above?

Thanks in advance,

Nico

nickyvv · ‎03-06-2020

Hi @soptim_vond,

what would be the reason you want that monthly dataset? Is it for comparison reasons? Or for historical reasons?
Do you want to keep the last day of the month as a snapshot? What would be the exact requirements?

Depending on your answers I think there are several possibilities.

For example, you could duplicate you original query and aggregate it at the month level as a new query.

Did I answer your question? Mark my post as a solution!

Blog: nickyvv.com | @NickyvV

soptim_vond · ‎03-06-2020

Hi,

thanks for your reply.

I would like to have a monthly snapshot (e.g. the last day of the month) in order to be able to do some kind of point-in-time reporting. Thus, at the end of the first year I would like to have the original dataset/report containing current values on the one hand, and 12 monthly snapshots that can be used to display historical development of the report´s data.

One solution that I have currently implemented is based on the "analyze in excel" feature and a tool called "power update", that can do scheduled refresh of workbooks.

I created an excel file that pulls the relevant information from the report (thus the kpi´s I want to track) via analyze in excel. I then use power update to refresh the excel file once a month and save it with an appended timestamp. Then I created a seperate PBI-Report using the folder with all the excel files as datasource.

This gives me the desired outcome, however I would prefer a solution that does not involve these nasty workarounds using excel and third party tools but relies solely on PBI-features such as dataflows.

Does that help understanding my intention? 🙂