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
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.

Forum Discussion

Creating a new table to normalize records from a second table

Related content

Table Merge creates duplicate records

Normalize monthly financial records into multiple records for producing line graph

Normal Distribution

Creating a record from list values

Counting Non-Blank Records