Forum Discussion

AnilKumar's avatar
AnilKumar
Helper II
6 years ago
Solved

repeat column values based on another column

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 Ord...
Data Modeling
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    6 years ago

    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 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

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's avatar
    AnilKumar
    Helper II
    6 years ago
    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