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.
rajendraongole1's avatar
rajendraongole1
Super User
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.
  • ClausS's avatar
    ClausS
    Helper I
    1 year ago

    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

  • ClausS's avatar
    ClausS
    Helper I
    1 year ago

    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