Create two filters from one dimension?

[simon20]

I have a facts table containing balances per day, configured as start and end. The fact table looks like this:

Load

Dimension,

Day,

Start/End,

Balance,

TimeStamp

FROM xx;

What I want to do is compare the end balance to the start balance each day to get a difference. The problem I have is that for each day, there might be several start or end balances. So, the user is allowed to filter on the balance timestamp to select the "correct" one. The problem I have is that I need the TimeStamp filter to separate on Start/End, so if I filter on a timestamp where Start/End = 'end', the start balance shouldn't be affected and vice versa.

The only solution I found to this is to split the fact table into two based on the start/end dimension, store balance and timestamp as StartBalance/EndBalance and StartTime/EndTime and then link them through a composite key of Dimension and Day. Then I can create two separate filters for the timestamp that does not affect each other.

This does not feel like the best solution, and I was wondering if there is a way to create the same without splitting the fact table and then linking/joining.

[dplr-rn]

can you share a sample of how the data looks for a couple of days for one account?

[simon20]

See attached for two examples. So:

• The first day we only have one start row and one end row for each dimension, so for this day we want to see the balance at start and end for those timestamps.
• 2019-04-02 has two timestamps for End.
  • If no filter for TimeStamp is selected, the latest one should be display (TimeStamp 23:59). The start value must however still be shown (07:51)
• 2019-04-03 has two timestamps for End and two for Start.
  • If not filter is selected, the biggest timestamp for End and Start should show (07:47 and 23:57)
  • If you filter on one of the End timestamps, the start value should not be affected
  • If you filter on one of the Start timestamps, the end value should not be affected
  • You need to be able to filter on both start and end timestamps, so the end filter selection should not "remove" possible start timestamp selections for that day.