Calculating expected revenue over time where prices change

Hey,
Where we work (a hospital providing rehab services), we have one system which records patients attending and receiving their rehab treatment, and a completely different system which is in charge of the billing/accounting. It seems that these aren’t linking up correctly, so I’m trying to make a report which shows how much revenue we should be getting based on the services provided to the individual patients. With this information we can try to track down discrepancies between the work we have done and what income we should be generating.

The wrinkle is that our prices occasionally change, so it’s not as simple as multiplying a visit by a number to reach a value for expected revenue for each row in the “factPatientVisits” table (for each of the different visit types).

The solution I thought of was having a separate price table with a primary date column, and then each other column is each of the visit types, and their associated price on each date. Then I would look up the visit against both the visit type, and the visit date to get the expected price, multiply the two and arrive at expected revenue.
This works, but it seems terribly inefficient to have so many duplicated values in this pricing table, since we have only changed our pricing a couple of times in the last 13 years.

Any suggestions for a more elegant approach? I think I am missing something fundamental here.
Thanks in advance,
Rod

@rodwhiteley,

Excellent article below with 3 different approaches to handling your slowly changing dimension issue, and the pros and cons of each:

Hope this is helpful.

  • Brian
1 Like

Thanks Brian,
The article was very helpful, and in another piece of good luck and timing, Mike Girvin (@Excelisfun) has just put up the first in a series of 4 on dealing with slowly changing dimensions in Excel, Power BI, and Power Query. As ever, these are great resources. Here’s the first in the series:


Rod
1 Like