Compare Daily Snapshot? Maybe by Journal, WriteBack, or Recording Results?

[Anonymous]

I was doing some searching and the last I could find on Write Back functionality was back from 2017.  Has this been addressed or is there some other alternative approach that might be available to log a data entry to compare a snapshot?

Essentially, everytime the program is open (or at most once a day), I want to log the total revenue being forecasted in the report for the next 90 days.  I would then include this dataset in another report to see how the forecasted revenue has changed from day to day.

Maybe something creative in Power Query with a Spreadsheet / Excel document?

[dax]

Hi @Anonymous , 

Did you mean that you want to date range from today to next 90 when each day refresh ? If so, you could try to use below M code to see whether it work or not

= Table.SelectRows(#"Converted to Table", each [Column1] >= DateTime.Date(DateTime.LocalNow()) and [Column1] <= Date.AddDays(  DateTime.Date(DateTime.LocalNow()),90))

 In addition, you also could refer to https://bielite.com/blog/write-back-to-sql-database-from-power-bi/  for details.

Best Regards,
Zoe Zhi

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

[Anonymous]

Hi @dax, I was referring to data that changes throughout the year.  For example, I might want to view the total of CURRENTLY unpaid invoices for this May against that of the invoices which were unpaid when the report was ran last May.  The data isn't stored, it's calculated based off whether the invoice is marked as paid or not.  Once cleared the flag is flipped and will never show on the report again.

unfortunately, the example code would require the date to be available or marked in the data.  However, thank you for providing it.  I'll read up on this link mentioned.

[mahoneypat · Accepted solution]

Sounds like you'd like to get periodic snapshots of your data, which isn't a feature in Power BI.  However it can be done in at least two ways that I know of.  Both are a little tricky, so it is not an easy solution (but it is doable for the initiated).

1. Publish your dataset and set up refresh.  Make a paginated report, publish it, and subscribe to it.  Use a flow to get the CSV, XML, or Excel file attachment from the email and store it (and name it) somewhere on OneDrive or SharePoint.

2. Create a table in query, convert it to JSON and send it in an http request to flow.  Use the flow to create a CSV (or other type of file), and save it to OneDrive or SharePoint.  This way may have issues setting up automatic refresh on the service (anonymous http POST request but there are some tricks to avoid that potentially with relative path, etc.).

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

@mahoneypat, this is a AWESOME suggestion.  I was wondering if it would be doable and even experimented with Flow to learn a little bit of it weeks earlier.  I had actually gotten pretty far, but I had some issue with the naming of the reports.  However, now that I recall it better, the test was slightly different as I was getting the report directly from the host system rather than Power BI.

I'll give this another shot in the next day or two.  However, it is very encouraging to hear that others could make it happen!