Question

Power BI Retention Policy – How to Implement FIFO? – What’s the best way to set up a basic FIFO (First-In-First-Out) retention policy in Power BI?

I need to implement a retention policy in Power BI that follows the FIFO (First-In-First-Out) approach. The goal is to automatically remove or archive older records while keeping the latest data. I’ve explored options like Power Query transformations and DAX, but I’m unsure of the best approach. Can anyone share a practical implementation method?

Answer 1

Implementing a FIFO (First In, First Out) retention policy in Power BI requires the effective management of data storage such that older records would be automatically removed or archived while keeping the latest data. This can be achieved in many ways:
Data Filtering using Power Query (M Language) 
In Power Query, filter older records dynamically using the Date column.
This relative date filter would keep just the latest n-days, weeks, or months of data.
Example: Table.SelectRows(Source, each [Date] >= Date.AddDays(DateTime.LocalNow(), -90)) keeps records of the last 90 days only 

Dynamic filtering through Dax 
This would be applicable where data retention was managed through reports and not at the source. This can be achieved by creating a DAX-calculated table or measure that only shows the latest records.

LatestData =

 VAR MaxDate = MAX('Table'[Date]) 

RETURN FILTER('Table', 'Table'[Date] >= MaxDate - 90)  

It guarantees that current records are involved in visualizations without changing the dataset. Retention at database levels or data source levels: Where Power BI connects to a database, consider implementing an SQL-based FIFO policy by scheduling a query that deletes or archives old records in the database before refreshing Power BI. 
Example SQL query for FIFO:

DELETE FROM SalesData

 WHERE Date < (SELECT MAX(Date) FROM SalesData) - INTERVAL 90 DAY;

The best approach depends on whether you want to filter data before importing (Power Query/SQL) or inside Power BI (DAX). For large datasets, handling retention at the source (SQL or Data Lake) is more efficient. Let me know if you need a specific implementation!