cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

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

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

View solution in original post

4 REPLIES 4
Highlighted
Community Support
Community Support

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

Hi @bflury , 

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.

Highlighted
Helper IV
Helper IV

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

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.

Highlighted
Community Champion
Community Champion

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

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

View solution in original post

Highlighted
Helper IV
Helper IV

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

@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!

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021