Creating transactional data from two separate reports in order to create a different view

Hi again,

I've been doing some thinking and suspect that my solution description may still be a bit cryptic. 🤔
I will try to add a few parts.

The goal is to be able to create visuals like this (or something similar):

To do that, you need a table with such raw data:

Using the Query Editor, it would be the following steps:

A. Prepare the _Contracts query and the _OverviewPerYear query (as described in the previous post).

B. Create a query with months for the relevant period (_MonthCalendarTable) similar to this:

C. Merge (as a new query RightsPerMonth) the _OverviewPerYear query and the _MonthCalendar query with left outer join over the Year column.

D. Merge RightsPerMonth query and the _Contracts query with left outer join over the Person column.

E. In RightsPerMonth create a custom column (IsRelevant) that is always 0 if the DateBasic is before the StartDate of the contract or if the DateBasic is after the EndDate of the contract. Otherwise the column gets a 1.

F. Filter by value 1 in the column IsRelevant. (Than you can delete the column again.)

With DAX you add the following columns to the created RightsPerMonth table:

• Number of Months
• Rights per Months

Calculated column Number of Months (very similar to the post before):

Number of Months = 

VAR _StartDate =  RightsPerMonth[_Contracts.StartDate]

VAR _StartMonth = MONTH(_StartDate)

VAR _StartYear = YEAR(_StartDate)

VAR _EndDate = RightsPerMonth[_Contracts.EndDate]

VAR _EndMonth = MONTH(_EndDate)

VAR _EndYear = YEAR(_EndDate)

VAR _YEAR = RightsPerMonth[Year]

VAR _NumberOfMonths =
SWITCH (
    TRUE(),
    _StartYear > _YEAR, 0,
    _EndYear < _YEAR, 0,
    _StartYear < _YEAR && _EndYear > _YEAR, 12,
    _StartYear = _YEAR && _EndYear > _YEAR, 13 - _StartMonth,
    _StartYear < _YEAR && _EndYear = _YEAR, _EndMonth,
    _StartYear = _YEAR && _EndYear = _YEAR, _EndMonth + 1 - _StartMonth)

RETURN
    _NumberOfMonths

Calculated column Rights per Months:

Rights per Months = 

VAR _RightsPerMonth =
    DIVIDE(
        RightsPerMonth[Rights build up],
        RightsPerMonth[Number of Months]
    )

RETURN
    _RightsPerMonth

Voilá, your fact table.

If you have people with multiple contracts who also change conditions during the year, you will still need to adjust the solution a bit.

I hope it helps.

Kind regard