Create "snapshots" on changing data

[jlie]

Hey!

I'm new to PowerBI and currently evaluating it for a complete roll-out in our company (so only a PowerBI free license, if this matters). I hope you can help me with this issue, because it is pretty critical.

I am looking for a functionality similar to salesforce snapshot reports. We report on some customer segments from our production environment on a weekly basis (not via salesforce, so I cannot implement this as a snapshot in salesforce and then query the snapshot). What I'm doing is basically applying some filters (last login, revenues, last action in plattform, etc) to the user accounts and segment them to A/B/C/etc. However, the filters are too complexe to reproduce the results with today's data. Therefore, I just query the reports on a weekly basis and manually copy the results into my "data table" that feeds my excel report. In excel we report on the trending snapshots per segment, e.g. "100 customers in Week 1", "95 in week 2", 105 in week 3", and so on.

The search found me some threads where one could solve the problem with a calculated column that could backwards calculate how many items were "relevant" at the given time. However, I don't think that this would be feasible, since I can not reproduce the segments from 2 months ago with today's data.

Do you have any idea how I could implement this? Is there a way to create a table for the results and sort of "append" the results of a query with a timestamp of that query? And everytime the query gets executed, it writes the row with the respective timestamp of execution into the table?

Best regards and many thanks for any ideas,

Jannis

[Greg_Deckler · Accepted solution]

Power BI does not have incremental load at present. This means that if you try to timestamp something at the time of query load, you will get updated values for the timestamp when it is refreshed. You might be able to hack around this but I can't think of a creative way at present.

One solution might be to pull each week's data in as a separate table and have a custom column either in M or DAX that puts in today's date. Load the data, which will populate the field and then disable load and refresh for that query. You would create a new query every week by essentially copying the query, pull the data, disable load and refresh.

Then, you would have an append query that appended all of these separate tables into a single table. In this way, you would have what you want, albeit, with a bit of manual work each week to create the new query and add it to the Append query but that's like 15 minutes of work each week really.

[Anonymous]

Hi Greg,

Any solutions to this issue with the current version of PowerBI?

Working on a similar problem...

[mwaters4]

ugggghhhh... that sounds awful...

we have daily operating reports that are created in excel and distributed in a similar fashion as the original poster.  There is no way possible for us to use Power BI and use this "work around" as that would be insanely time intensive and way overkill.

Why cant we just have the ability to schedule a send as an attachment like a pdf or excel file, etc. so we can have a snapshot version?

Surely there is another way... 

[Anonymous]

I am new to Power BI and was wondering if you found a solution to the snapshot report issue...We send daily reports in excel/pdf and I need that same functionality in Power BI

Thanks