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

Hi, 

you can import both the tables individually in Power BI desktop and try to build a data model using primary and foriegn keys. 

Once your data model is ready, data visualization is easy. 

Here's a guide to get started: What is a Data Model in Power BI? - Power BI Training Australia

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

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