Automatically delete rows with expired dates

[LFM]

Hello.  I have a query that shows  trade date and exposure with deliver from and delivey to. Exposure show values and delivery to and from is dates. The table is connected to annother query with calender dates. I want to automatically delete all the rows that shows delivery to before todays date.

Trade date	Exposure	Delivery from	Delivery to
23.07.2024	23432423	01.05.2024	31.05.2024
22.07.2024	324324	01.07.2024	31.07.2024

The first row should then be deleted from the query. 

Is it possible to make this happen auctomatically as delivery date expires? 

[AmiraBedh · Accepted solution]

You cannot delete data but you can use a column to filter your data. You can add a custom column to compare dates :

Date.FromText([Delivery to]) < Date.From(DateTime.LocalNow())

 Name it as IsExpired to use it as a flag, the result will be true or false.

Then in your table filter out expired rows by unchecking true.

Each time your dataset is refreshed you will not see these rows.

[Anonymous · Accepted solution]

Hi @LFM 

Power Query method:

Firstly filter by [Delivery to] column based on a specific date, then modify the formula like below in formula bar. Date.From(DateTime.LocalNow()) represents today's date. 

= Table.SelectRows(#"Previous step", each [Delivery to] >= Date.From(DateTime.LocalNow()))

If you want it to be updated every day, you need to refresh the report daily or set a schedule refresh to do that in Power BI Service.

 DAX method:

Create a measure like below and use it as a filter on the visuals where Delivery to is included. When you open the report, it will be updated. 

Measure = IF(SELECTEDVALUE('Table'[Delivery to])<TODAY(),0,1)

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[AmiraBedh · Accepted solution]

You cannot delete data but you can use a column to filter your data. You can add a custom column to compare dates :

Date.FromText([Delivery to]) < Date.From(DateTime.LocalNow())

 Name it as IsExpired to use it as a flag, the result will be true or false.

Then in your table filter out expired rows by unchecking true.

Each time your dataset is refreshed you will not see these rows.