Getting Historical Data into a PowerMetric

PowerMetrics automatically accumulate data over time, beginning with the data used on initial creation. When building a PowerMetric, you may want to include historical data as well as collecting and tracking data going forward. To do this, you’ll need to start your PowerMetric with what we call “backfilled” data and then add the current data. There are 2 ways this is done, depending on how the backfilled data is added to the PowerMetric, either by 


  • file upload (this approach works only for Transactional Values and Periodic Summary data shapes)
    • 1 PowerMetric for the historical data file uploads (PowerMetric1)
    • 1 PowerMetric for the current data (PowerMetric2)
    • 1 calculated PowerMetric that combines the previous 2 PowerMetrics
  • back-dated or paginated API queries
    • 1 PowerMetric for both historical and current data


In either case, the data must be added to a PowerMetric in chronological order from oldest to newest.


File Upload

The data sources for the historical data and the current data must be modelled such that the models have same column header names with the same data formats.


Back-Dated or Paginated API Queries

If the service used to create the PowerMetric allows you to retrieve historical data, you can create a single PowerMetric starting with the oldest data. As you build up the data in your PowerMetric, ensure all the data has the same format (same data in the same columns) the query should not change except for the dates or the page number.


  1. Create a data source with your oldest data and save it.
  2. Model this data source and create a PowerMetric based on the modelled data source.
  3. Update the data source with the next oldest data, either
       - upload the next oldest data file 
       - change the dates in the API query to the next time period or increment the page number
  4. Check your PowerMetric: set your Date Range filter to All Time and verify that the PowerMetric has automatically updated to show the new data.
  5. Repeat Steps 3 and 4 until all historical data has been added to the PowerMetric.
  6. You are now ready to get the current data using an API query that will continue to retrieve data in the future. If you are using the file upload method to backfill your PowerMetric, create a separate data source for your current data, otherwise, use the same data source used to retrieve your historical data. The query should return data up to today using Klipfolio’s date parameters, for example, {date.today.format()}. 
  7. If you are using the back-dated/paginated API query method to backfill your PowerMetric, go to Step 10. If you are using the file upload method to backfill your PowerMetric, continue with the next steps.
  8. Model the data source created for your current data and create a PowerMetric based on this modelled data source.
  9. Create a calculated PowerMetric using a formula that adds your 2 PowerMetrics (historical and current) together: PowerMetric1 + PowerMetric2.
  10. You are done! Your PowerMetric has historical data and will continue to automatically accumulate data going forward.


Please sign in to leave a comment.