Calculating total price across overlapping periods and multiple prices

I started this case thinking it would be straightforward to calculate. However, after what feels like an eternity of trial and error (and maybe a bit of imaginary beard stroking), I’ve hit a roadblock in calculating the total price for accommodation periods.

The case:
I’ve got 2 tables:

  1. Table Prices: A list of prices applicable to different periods.
  2. Table Accommodations: Information on accommodations for various periods.

The goal
Calculate the total price for an accommodation period, accounting for different prices within that timeframe.

This I proudly achieved, … kind of.

Where I need your expertise
Things get too scary for my imaginary beard when:

EDIT: a) Two prices have overlapping active periods (1-2 days). In such cases, the total should consider the price that has a higher price for the overlapping period.

b) There are accommodation period days with no corresponding valid prices in the Prices table. For these days, I aim to apply the price that concluded just before the blank (gap) days. This logic should extend to multiple periods within the accommodation.

For a clearer picture, I’ve put together a sample doc here:

I hope that this fantastic community with active seasoned experts will share wisdom and guidance.

sub a) what if the price in the 2nd period is lower, still use the higher prices of the 1st period?

Hi Joost,
From business perspective it should always use the higher price for the overlapping period.

Hmmm, that is kind of strange, because in your earlier text you said:

a) Two prices have overlapping active periods (1-2 days). In such cases, the total should consider only the price that starts earlier and transition to the second price only when the first one concludes.

And is this only limited to a max of 2 days? In your example the overlap is 5 days and in your correct answer you use the lower price (15 x 50).

I am willing to look at your problem, but it should be clear what has to be accomplished.

Creating a daily price table will make the calculations MUCH easier. (But still letting you use your price range table)

I just published a new pack yesterday (Fake Data Generator) that will help with this.

I put together a demo for you. It shows you which prices it uses for the calculations so you can validate the prices.

Let me know if I can help further with this!



hi @Troy_Larson ,

I am afraid I don’t understand how your pack operates in this context, would you mind clarifying it? second the use case is a complex one because one date can have two prices, so there is overlap. @joost_mineur asked for clarification in this context.

A standard set up to define prices for goods & services would relate prices to periods without overlap: one day and one price. Overlap requires an additional business rule, because you can have more than one overlap in this logic, maybe 3 or 4.

Cheers, Christiaan

I lay this out in the demo.

  • Using the pack, I generated a new Daily Prices table with one row for each date from min(Valid From) until max (Valid Until)
  • Each row has a calculated column with the price for that day based on @joost_mineur’s comment about the overlapping: If there is one price for that day, use that. If there are two prices, use the more expensive (or least expensive). If there are NO prices for a date, use the most recent price.

From here, it’s really simple to just sum up the price by filtering on the Arrive/Leave date.

And we can also generate a simple Calculations column that shows how the prices were calculated.

In essence, my point to demonstrate that the price column on the Daily Prices table contains all the logic for calculating the price for a specific date. It’s much less complicated than trying to generate this logic from the date range.

1 Like

mabye you can consider to alter the settings?

Sorry, this is fixed now

1 Like

Thanks for pointing this out. I updated my original post to be clear that when overlapping the higher price should apply for the overlapping days.

It won’t be limited to 2 days overlap. It might be a lot more days because it would be sales rep decision.

I’ve updated the column with the manual calculation in my sample doc.

That’s exactly what I originally intended. But the sales rep had different thing in mind.

I used the following setup:

Seems linke your formula fails…

1 Like

Troy, thanks for the contribution. I am trying to understand how the pack would act with dynamically changing data for hundreds of accommodations and prices :slight_smile:

Hello @Stefan_Stoyanov ,

I think your “Correct answer should be” is not correct. Just looking at Person 1, checking in on Jan 1, the rate would be 50 euro. Up to Jan 9, the rate would stay the same, so that is 450 euro.
On Jan 10, the rate becomes 100 euro.
So the totals are a bit different from what you think.
The formula that works is like below. It’s not a beauty, but what you are asking is not very standard.
I requested edit access to your doc and I will add a column with the correct totals (I think) upon granted access.

edit (2) - corrected formula (added one more max() to catch exceptional situations)

1 Like

:pray: Thanks, master Joost, for the solution!

In this shared doc in column JM -… is the final formula.

Based on what I’ve learned, I created an additional scenario that displays a message when not all dates have corresponding prices. In the event of relevant prices being added to the Price table, the formula calculates the total by considering all the applicable prices. In cases of overlapping prices, the formula prioritizes the one with the higher price.

:warning: Both formulas take over a second to calculate. For my real doc with hundreds of rows and thousands of prices, I consider triggering the calculation with a button.

Hey @Stefan_Stoyanov , I LOVE solving problems like this . . .

I revised the demo I posted earlier based on @joost_mineur and your formulas.

This is the Price Table that gives you calculated price for each day. Here’s the benefits to using a price table approach rather than a single formula:

  • You can validate the logic to make sure the right price is being used for the right days
  • This gives you a simple price sheet where you can lookup the price for any given day
  • You could export the price sheet to a spreadsheet, DB, etc.

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.