Log Weekly Results into New Table

[Anonymous]

Hello Everyone,

I have a report in the Power BI that shows the total compliance percentage of the security installed of each location we have around the factory.  The report refreshes daily and the data comes from an SQL table.

I would like to log the results from the report each week into a new table and then use that table to show a change in historical compliance percentage every week. 

Any help would be greatly appreciated.

[v-robertq-msft]

Hi, @Anonymous 

If the total compliance percentage is calculated using the measure in Power BI, I don’t think the value of the historical compliance percentage can be inserted into a new table every week. Because the data is updating and the measure can only calculate based on the current data. The historical data can not be recorded in Power BI.

Maybe you can find the solution using procedures in SqlServer.

Best Regards,

Community Support Team _Robert Qin

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

[Calle487]

Hi,

I have the same issue/need as the thread starter.

I have an activity tracker with a column "Activities" and a column with "Status".

The report refreshes daily and the data comes from an Excel file stored in a sharepoint folder.

I would like to log the results from the tracker each week into a new table and then use that table to show a change in historical completion every week. I don't use any measures to the data. Is it possible to set up a process in Power BI where it automatically stores the data in a log table each week on Monday 6 am for example? If not, would it be possible to store the log data somewhere else using some other tool?

Thanks!

[lbendlin]

Power BI has no memory. You need to do that in the upstream system.  You already have a sharepoint folder. Use that for storage, and then use Power BI to combine the daily files (via the Sharepoint Folder connector)

[Calle487]

Thank you @lbendlin for the quick reply. Yes, I think I'll have to use the sharepoint folder as storage and sync a separate log file to the Power BI report. Cheers!

[Anonymous]

Hello Robert,

Thank you for your reply.

Yes, you are correct the total compliance percentage calculation is happening using the measure. 

Have you ever tried this via procedures in SQL server?

[v-robertq-msft]

Hi, @Anonymous 

If you want to implement this function in Sqlserver, I think you need to create a historical data table first, then write a stored procedure to insert data into the historical table, and set up a timing job for this stored procedure to execute it regularly. For a more specific implementation process, I suggest you to go to the SqlServer community to open a case so that experts in SqlServer can help you in advance.

Best Regards,

Community Support Team _Robert Qin

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

[lbendlin]

why new table?

Your data acquisition process sounds exactly like what incremental refresh is about.

[Anonymous]

Hello,

Thank you for your reply.

We are calculating the compliance percentage at the PowerBI side and only the raw data is coming from the SQL from which we are doing all the calculations.