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

Hi Anonymous,

I think the following would be a possible approach.

I'll simplify your problem a bit, but I hope the basic idea becomes clear.

What I understood 

You have a table with data about the contract start and end. E.g. like this (Query Editor):

Let's call this table "Contracts".

You also have a table with the relevant people and years and some futher information. Like this one (Query Editor):

Let's call this table "Overview per year".

You would like to have a table that shows per year/person how many months the person worked that year according to the contract information. Similar to this:

Wenn du diese Tabelle zur Verfügung hast, kannst du die Berichte darauf aufsetzen.

Possible Solution

A possible solution consists of three steps:

1. supplementing the "Contracts" table with Power Query
2. define relation in Power BI Desktop
3. create calculated column with DAX

Step 1:

Replace the null-values with appropriate start or end dates.

E.g.:

Step 2:

Create a 1/n-relationship between the two tables based on the person columns.

Step 3:

Create the new column with DAX.

The following code is quite small-step, but this is how it becomes clearest.

Number of Months = 

VAR _StartDate = 
     CALCULATE (MIN (Contracts[StartDate]))

VAR _StartMonth =
    MONTH(_StartDate)

VAR _StartYear =
    YEAR(_StartDate)

VAR _EndDate =
    CALCULATE (MIN (Contracts[EndDate]))

VAR _EndMonth =
    MONTH(_EndDate)

VAR _EndYear =
    YEAR(_EndDate)

VAR _YEAR =
    'Overview per year'[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

An important note: The CALCULATE() in line 4 and 13 is important so that the line information is used as a filter context for the calculation (context transition). The rest is long but simple.

I hope, this helps.

Kind regards