Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Log Weekly Results into New Table

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.

Need Help

8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    why new table?

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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. 

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    5 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        v-robertq-msft
        Community Support
        5 years ago

        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.

    • Calle487's avatar
      Calle487
      Frequent Visitor
      3 years ago

      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's avatar
        lbendlin
        Super User
        3 years ago

        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)