Building a trending table based off latest refresh

[Anonymous]

I'm looking for some advice on the best way to build a table that is populated with a value that changes at ever refresh.  Ideally, everytime the report is refreshed, an entry with the date and time and the "AverageDaysOpen" calculation at the time will be added.  I'll then use that table to use for some trend analysis. 

[v-xuding-msft]

Hi @Anonymous ,

Do the answers make sense? If so, please mark the helpful answer as the solution. More people will benefit from here. If you still have questions, please feel free to ask us.

[mahoneypat]

This is no out-of-the-box features that would let you do this.  The easiest way I can think of would be to publish to the service, make a dashboard, create an Alert that is always above the trigger threshold to send the date/time (email date/time) and value to trigger a Flow, parse your value from the email test, write your data to an Excel table or SharePoint list (that you can then consume in Power BI).

https://docs.microsoft.com/en-us/power-bi/create-reports/service-set-data-alerts

You can also send your data directly to Flow from Power BI.  It is more involved but doable and more flexible.

https://www.thebiccountant.com/2019/04/01/export-data-from-power-bi-using-microsoft-flow/

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

Regards,

Pat

[Anonymous]

@mahoneypatunfortunately it's not a streaming data source, so there is no option for the alerts

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

You could use the function of DateTime.LocalNow in Query Editor to get the current date and time on the system. And then use it to do calculation.

Display Last Refreshed Date in Power BI 

How to Add the Last Refreshed Date and Time to a Power BI Report 

[mahoneypat]

The two approaches I suggested do not require streaming datasets.

Regards,

Pat