Forum Discussion

ClausS's avatar
ClausS
Helper I
1 year ago
Solved

Creating a new table to normalize records from a second table

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 Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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.
Sahir_Maharaj's avatar
Sahir_Maharaj
Super User
1 year ago

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]
)