Forum Discussion
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 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?
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]))RETURNSELECTCOLUMNS(ExpandedTable,"RecID", TableA[RecID],"Name", TableA[Name],"StartDate", [GeneratedStartDate],"EndDate", [GeneratedEndDate],"Value", [ProratedValue],"Years", 1)Hope this works please check.
5 Replies
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]))RETURNSELECTCOLUMNS(ExpandedTable,"RecID", TableA[RecID],"Name", TableA[Name],"StartDate", [GeneratedStartDate],"EndDate", [GeneratedEndDate],"Value", [ProratedValue],"Years", 1)Hope this works please check.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
ClausI 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
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] )
