How to combine 3 values with diferent dates?

[JoseHop]

I have 3 tabels 

ID	StartDate	EndDate	SortContract	Duty
502538	1-1-2022	31-12-2099	Permanent	3499

ID	StartDateHours	EndDateHours	Hours
502538	1-1-2022	15-2-2022	20
502538	16-2-2022	31-12-2022	15
502538	1-1-2023	30-6-2023	25
502538	1-7-2023	31-12-2099	30

ID	StartDateHours	EndDateHours	Company
502538	1-1-2022	31-12-2022	Boat
502538	1-1-2023	31-8-2024	Bike
502538	1-9-2024	31-12-2099	Car

Now I want to see in a visual:

	jan-22	jan-23	jan-24
SortContract	Permanent	Permanent	Permanent
Duty	3499	3499	3499
Hours	20	25	30
Company	Boat	Bike	Bike

I have a Calendar to connect to in a semantic model. But I can only connect with one tabel.
How can I get the wanted result? 

[Anonymous]

Thanks for the reply from rajendraongole1 , please allow me to provide another insight:

Hi, @JoseHop 

rajendraongole1's method is a good solution. If their solution has helped you, please accept their post as the solution.

Here is my solution:

1.Firstly, I created the following table and filled in the values you need.

2.Secondly, I created the following calculated columns based on your requirements.

SortContract = 
CALCULATE (
    MAX ( 'Table'[SortContract] ),
    FILTER (
        'Table',
        'Table'[StartDate] <= 'final'[date]
            && 'Table'[EndDate] >= 'final'[date]
    )
)

Duty = 
CALCULATE (
    MAX ( 'Table'[Duty] ),
    FILTER (
        'Table',
        'Table'[StartDate] <= 'final'[date]
            && 'Table'[EndDate] >= 'final'[date]
    )
)

Hours = 
CALCULATE (
    MAX ( 'Table (2)'[Hours] ),
    FILTER (
        'Table (2)',
        'Table (2)'[StartDateHours] <= 'final'[date]
            && 'Table (2)'[EndDateHours] >= 'final'[date]
    )
)

Company = 
CALCULATE (
    MAX ( 'Table (3)'[Company] ),
    FILTER (
        'Table (3)',
        'Table (3)'[StartDateHours] <= 'final'[date]
            && 'Table (3)'[EndDateHours] >= 'final'[date]
    )
)

3.Lastly, I created the following calculated table.

FTable = 
UNION (
    SELECTCOLUMNS (
        'final',
        'final'[date],
        "type", "SortContract",
        'final'[SortContract]
    ),
    SELECTCOLUMNS ( 'final', 'final'[date], "type", "Duty", 'final'[Duty] ),
    SELECTCOLUMNS ( 'final', 'final'[date], "type", "Company", 'final'[Company] ),
    SELECTCOLUMNS ( 'final', 'final'[date], "type", "Hours", 'final'[Hours] )
)

4.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JoseHop]

Hi @Anonymous, 
Thank you for this solution.
Apologies for the late response.
I have not yet managed to implement your solution.
Now I discovered that I forgot an important part:
It is about creating this overview per ID. The ID is a person.
There is only 1 person in the example (5025381). In my data I have 3000 persons.
So I would like to know this per person.
How do I have to adjust your solution?

ID/ Person	type	jan-22	jan-23	jan-24
5025381	Company	Boat	Bike	Bike
5025381	Duty	3499	3499	3499
5025381	hours	20	25	30
5025381	sortContract	Permanent	Permanent	Permanent

[JoseHop]

@Anonymous Could you look into this please? 
Thank you for your help so far, Jose

[Anonymous]

Hi, @JoseHop 

Thank you for your prompt response.

Here is the solution based on your latest requirements:

1.Firstly, we will create a date table to retain the time you need on the visual object, named datetable:

2.Secondly, create the following calculated table to aggregate all the data as an intermediate table:

TF = CROSSJOIN(VALUES('datetable'[date]),VALUES('Table'[ID]))

3.Next, use the following calculated columns:

Company1 =
CALCULATE (
    MAX ( 'Table (3)'[Company] ),
    FILTER (
        'Table (3)',
        'Table (3)'[StartDateHours] <= 'TF'[date]
            && 'Table (3)'[EndDateHours] >= 'TF'[date]
            && 'Table (3)'[ID] = EARLIER ( 'TF'[ID] )
    )
)

Duty1 =
CALCULATE (
    MAX ( 'Table'[Duty] ),
    FILTER (
        'Table',
        'Table'[StartDate] <= 'TF'[date]
            && 'Table'[EndDate] >= 'TF'[date]
            && 'Table'[ID] = EARLIER ( 'TF'[ID] )
    )
)

Hours1 =
CALCULATE (
    MAX ( 'Table (2)'[Hours] ),
    FILTER (
        'Table (2)',
        'Table (2)'[StartDateHours] <= 'TF'[date]
            && 'Table (2)'[EndDateHours] >= 'TF'[date]
            && 'Table (2)'[ID] = EARLIER ( 'TF'[ID] )
    )
)

SortContract1 =
CALCULATE (
    MAX ( 'Table'[SortContract] ),
    FILTER (
        'Table',
        'Table'[StartDate] <= 'TF'[date]
            && 'Table'[EndDate] >= 'TF'[date]
            && 'Table'[ID] = EARLIER ( TF[ID] )
    )
)

4.Finally, create the following calculated table to derive the final data structure:

FTable =
UNION (
    SELECTCOLUMNS (
        'TF',
        'TF'[date],
        'TF'[ID],
        "type", "SortContract",
        'TF'[SortContract1]
    ),
    SELECTCOLUMNS ( 'TF', 'TF'[date], 'TF'[ID], "type", "Duty", 'TF'[Duty1] ),
    SELECTCOLUMNS ( 'TF', 'TF'[date], 'TF'[ID], "type", "Company", 'TF'[Company1] ),
    SELECTCOLUMNS ( 'TF', 'TF'[date], 'TF'[ID], "type", "Hours", 'TF'[Hours1] )
)

5.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1]

Hi @JoseHop - you can create a new table that combines the data from all three tables into a single unified table. This combined table will allow you to show SortContract, Duty, Hours, and Company for each period.

I am using TAB1,TAB2 and TAB3 , you can replace it as per your model

CombinedTable =

UNION(

    SELECTCOLUMNS(

        'TAB1',

        "ID", 'TAB1'[ID ],

        "Date", 'TAB1'[StartDate],

        "Attribute", "SortContract",

        "Value", 'TAB1'[SortContract]

    ),

    SELECTCOLUMNS(

        'TAB1',

        "ID", 'TAB1'[ID ],

        "Date", 'TAB1'[StartDate],

        "Attribute", "Duty",

        "Value", FORMAT('TAB1'[Duty], "General Number")

    ),

    SELECTCOLUMNS(

        'tab2',

        "ID", 'TAB2'[ID ],

        "Date", 'tab2'[StartDateHours],

        "Attribute", "Hours",

        "Value", FORMAT('tab2'[Hours], "General Number")

    ),

    SELECTCOLUMNS(

        'tab3',

        "ID", 'TAB3'[ID ],

        "Date", 'tab3'[StartDateHours],

        "Attribute", "Company",

        "Value", 'tab3'[Company]

    )

)

Since the tables may have gaps (e.g., SortContract remains the same across periods), use DAX measures to handle "forward fill":

LatestValue =

CALCULATE(

    MAX('CombinedTable'[Value]),

    FILTER(

        'CombinedTable',

        'CombinedTable'[Date] <= MAX(Calendar[Date]) &&

        'CombinedTable'[Attribute] = SELECTEDVALUE('CombinedTable'[Attribute])

    )

)

Hope this helps.