Forum Discussion
Storing Tables with Calculated Columns as Static Tables
Hi,
I'm working on a revenue forecast dashboard. The main table is Opportunity (with Id, Created Date, Close Date, Amount, etc. columns). I created several other tables from this table that aggregate open and closed opportunities. These tables then have calculated columns that make up the forecast model. Column examples: average sale price, daily average opportunity count, and moving average of closed won. Once live, the dashboard will be refreshed daily.
My question: is it possible to have a static copy of these tables (like a snapshot) within Power BI? I'd like to keep the tables static once calculations are made for trend analysis. I read about disabling load, but I don't think the approach works, as refresh happens daily with new data to be included in the calculation.
I'm exploring Power Automate to export the data to Excel as an alternative for now. It's my first time using Power Automate so the flow creation / Power BI alert linking has been quite challenging, so any input is appreciated.
Short answer is no. Power BI has no memory.
Long answer: You can run DAX queries against a semantic model, even from within the Power Query Editor of that same semantic model. That is called Self Referencing. Use at your own risk. You will get a giant headache when you think about it too much. There is also no safety net.
4 Replies
- lbendlin
Super User
Short answer is no. Power BI has no memory.
Long answer: You can run DAX queries against a semantic model, even from within the Power Query Editor of that same semantic model. That is called Self Referencing. Use at your own risk. You will get a giant headache when you think about it too much. There is also no safety net.
I'll read up on the self-referencing approach. Thank you for sharing.
Hi, atikahmd
Thanks for the reply from lbendlin , please allow me to provide addition:
Using Power Automate to export data to Excel, you can set up a process to export data to Excel. You can use the “Export to Excel” action to save your data to a specified location. You can check the following link:
How to export data in Power Automate to an Excel File
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I managed to export data to Excel; however, linking the trigger to a Power BI alert has been an issue. Thanks for sharing the link.
