repeat column values based on another column

[AnilKumar]

Hi all,

I am Struggling since long back. I have data like 

Order num	original date	actual date	invoiced amt times
Order1	June-2016	June-2016	1
Order1	sept-2016	march-2017	3
Order1	dec-2016	march-2018	2
Order1	march-2017
Order1	june-2017
Order1	sept-2017
Order1	dec-2017
Order1	mar-2018
Order1	june-2018

What i Need is a new column which will be like,

Order num	original date	actual date	invoiced amt times	NEw Colum
Order1	June-2016	June-2016	1	June-2016
Order1	sept-2016	march-2017	3	march-2017
Order1	dec-2016	march-2018	2	march-2017
Order1	march-2017			march-2017
Order1	june-2017			march-2018
Order1	sept-2017			march-2018
Order1	dec-2017
Order1	mar-2018
Order1	june-2018

So based on the No.of times column, the new column shold be derived.

Please suggest an idea.

Thanks in Advance.

Regards,

Vishnu priya

[Ashish_Mathur]

Hi,

Based on the 3rd and 4th columns, you want to generate the 5th column.  What is the use of the 1st and 2nd columns?

[AnilKumar]

Hi,
The first column is about the order number. There might be n number of orders. And the 2nd column tells the data about the month to be invoiced.
Based on this only the no.of records of particular order depends.
Thanks & regards ,
Vishnu priya

[v-qiuyu-msft · Accepted solution]

Hi @AnilKumar, 

You can create a index column in Query Editor firstly: 

Then create calculated columns below: 

EndRowNum = CALCULATE(SUM('Table'[invoiced amt times]),FILTER('Table','Table'[Index]<=EARLIER('Table'[Index])))

StartRowNum = 'Table'[EndRowNum]-'Table'[invoiced amt times]+1

Column = CALCULATE(MIN('Table'[actual date]),FILTER('Table',EARLIER('Table'[Index])>='Table'[StartRowNum]&&EARLIER('Table'[Index])<='Table'[EndRowNum]))

Best Regards,
Qiuyun Yu 

[AnilKumar]

Thanks. It worked for my case.