Creating a new table to normalize records from a second table

[ClausS]

Hi,

I need help with the following Problem:

Table A has records about service contracts and their value as well as covered time frame. I have contracts for 1 to 5 years. For better analytics, I need to create a new table which only has records valid for 12 month (1 yr) and the cotract value for that time frame.

My Main Table A looks like:

RecID		Name		StartDate		EndDate		Value		Years
1		Test1		01.01.2022		31.12.2022		$      10.000,00		1
2		Test2		22.06.2023		21.06.2024		$      50.000,00		1
3		Test3		01.01.2021		31.12.2024		$      90.000,00		3
4		Test4		14.02.2022		13.02.2024		$      40.000,00		2
5		Test5		01.05.2021		30.04.2025		$      50.000,00		5

The new Table should look like:

RecID		Name		StartDate		EndDate		Value		Years
1		Test1		01.01.2022		31.12.2022		$      10.000,00		1
2		Test2		22.06.2023		21.06.2024		$      50.000,00		1
3		Test3		01.01.2021		31.12.2022		$      30.000,00		1
4		Test3		01.01.2022		31.12.2023		$      30.000,00		1
5		Test3		01.01.2023		31.12.2024		$      30.000,00		1
6		Test4		14.02.2022		13.02.2023		$      20.000,00		1
7		Test4		14.02.2023		13.02.2024		$      20.000,00		1
8		Test5		01.05.2021		30.04.2022		$      10.000,00		1
9		Test5		01.05.2022		30.04.2023		$      10.000,00		1
10		Test5		01.05.2023		30.04.2024		$      10.000,00		1
11		Test5		01.05.2024		30.04.2025		$      10.000,00		1
12		Test5		01.05.2025		30.04.2026		$      10.000,00		1

Can someone help me?

[rajendraongole1 · Accepted solution]

Hi @ClausS - you can try below suggested solutions and request from @Sahir_Maharaj  and @uzuntasgokberk .

giving another alternative with a new calculated table logic as below

CalculatedTable =

VAR ExpandedTable =

    GENERATE(

        TableA,

        ADDCOLUMNS(

            GENERATESERIES(

                0,

                INT(DATEDIFF(TableA[StartDate], TableA[EndDate], MONTH) / 12),

                1

            ),

            "GeneratedStartDate",

                EDATE(TableA[StartDate], [Value] * 12),

            "GeneratedEndDate",

                EDATE(TableA[StartDate], ([Value] + 1) * 12) - 1,

            "ProratedValue",

                TableA[Value] / TableA[Years]

        )

    )

RETURN

    SELECTCOLUMNS(

        ExpandedTable,

        "RecID", TableA[RecID],

        "Name", TableA[Name],

        "StartDate", [GeneratedStartDate],

        "EndDate", [GeneratedEndDate],

        "Value", [ProratedValue],

        "Years", 1

    )

Hope this works please check.

[ClausS]

I added an IF Statement around the "INT(DATEDIFF(TableA[StartDate], TableA[EndDate], MONTH) / 12)" to make sure the Result is greater 0 and now it works. Data is never be perfect 🙂
Thx again for your great help.
Claus

[ClausS]

Thx for your work, I think something is missing. I get the error message "The arguments in GenerateSeries function cannot be blank."

Can we include a statement to make sure records with incorrcet data (Like no start or end date) will be skipped?

BR
Claus

[Sahir_Maharaj]

Hello @ClausS,

Can you please try this approach:

NormalizedTable =
ADDCOLUMNS(
    GENERATE(
        'Table A',
        VAR ContractStart = [StartDate]
        VAR ContractEnd = [EndDate]
        VAR ContractYears = [Years]
        VAR ValuePerYear = [Value] / ContractYears
        RETURN GENERATESERIES(1, ContractYears, 1)
    ),
    "YearStart",
        DATEADD([StartDate], (VALUE - 1) * 12),
    "ValuePerYear",
        [Value] / [Years]
)

[uzuntasgokberk]

Hello @ClausS ,

Filter years column to 1?

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk