Datamodel for two "independent" fact tables within a cashflow dashboard

[ingo_lsg]

Hi experts,

I have a datamodel problem.

My datamodel looks like this:

Table Cashflow:

Includes all cashflows (cf_amount) made on certain dates (cf_date)

Cashflows are very operational and we have a high volume of data here

Table Status:

Includes all status amounts(st_betrag) made on certain dates (st_stichtag)

Status amount is a cash balance per due day.

Table Namen:

In my original data model this is the company table. This is masterdata. Names maybe grouped.

All transactional data is linked via the names table.

Now I want to build the following sheet:

The first bar in the waterfall chart represents the sum of amounts (st_betrag) for a chosen date (st_stichtag).

Choosing an st_stichtag works fine.

A beginning balance of 1,7k is shown and the cashflow of 696 is added to this.

But when choosing a cf_date the beginning balance changes, because the names get filtered.

This shouldn't be the business case. The beginning status amount should always stay as it is and not be affected by any of the fields in the cashflow table and vice versa.

Only the name table fields should have an effect on both tables.

Assumptions:

- Problem shall be solved via the datamodell - no complex set analysis shall be used.

-  Maybe this could be solved via link table and generic key? Tried, but didn't manage to solve that yet.

- solution shall also work for other charttypes than waterfall chart

Attached app and data.

Please help!

ingo