Power BI Using Business Central - Backlog

Hello!

I am facing a challenge with calculating the historical backlog in Power BI using DAX. My objective is to determine the total backlog value at the end of each day, including orders that were open at that time but have since been posted and closed.

Currently, once an order is posted, it is removed from the open orders and recorded as a sale. This means I cannot view the total backlog from previous periods because those open orders are no longer visible—they are now closed sales. For example, when I try to assess the backlog for the same day in previous months, I cannot see the orders that were open at that time because they have been finalized and posted since then.

I've attempted to create relationships between the Posted Sales Invoices, Sales Orders, and a Sales Archive orders table, but I haven't been successful in creating a measure that accurately reflects the historical backlog.

I am seeking assistance to develop a logic in DAX that will allow me to display the total value of open orders in the past, even after they have been posted.

Any guidance or support with this would be greatly appreciated.

Thank you in advance for your help!