how to repeat the rows in a Datatable based on a column value

[Prabhu_MDU]

Hi, 

as per the image given below.. 

I want to repeat the row based on the StartDate and EndDate columns. 

In the Image:

the first output tells you the exact out put from the Databse tables.. 

the second output tells you the calculated out put from the Database tables..

what am I doing here ?.. 

well,

1) I am just calculating the calendar month which lies from the start date and ends before the Enddate

2) All the derived date values are End Of Month values.. 

3) Except the EOMDate colums, all the rest of the columns will remain the same.. 

what is expected !!

I want to know how can I do the same in power bi using DAX Queries.

Any help is much appreciated.

Thanks & Regards, 

Prabhu

[v-yuta-msft · Accepted solution]

@Prabhu_MDU,

You may refer to steps below:

Firstly, click Modeling-> New Table, repeat current rows using DAX below:

Table = 
UNION (
    Table1,
    Table1,
    Table1,
    Table1,
    Table1,
    Table1,
    Table1,
    Table1,
    Table1,
    Table1,
    Table1,
    Table1
)

 Secondly, create a calendar table which contains a series of date which is end of month:

Calendar = 
UNION (
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 0 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 1 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 2 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 3 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 4 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 5 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 6 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 7 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 8 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 9 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 10 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 11 ) )
)

Finally, generate the result table using DAX below:

Result = 
DISTINCT (
    SELECTCOLUMNS (
        CROSSJOIN ( 'Table', 'Calendar' ),
        "CLIENT_POLICY_NUMBER", 'Table'[CLIENT_POLICY_NUMBER],
        "StartDate", 'Table'[StartDate],
        "EndDate", 'Table'[EndDate],
        "PolicyYear", 'Table'[PolicyYear],
        "InforceDate", 'Table'[InforceDate],
        "CancelDate", 'Table'[CancelDate],
        "INSURED", 'Table'[INSURED],
        "DOM_STA", 'Table'[DOM_STA],
        "PROGRAM", 'Table'[PROGRAM],
        "PRODUCT", 'Table'[PRODUCT],
        "Company", 'Table'[COMPANY],
        "MGA_NAM", 'Table'[MGA_NAM],
        "Total_Poli", 'Table'[Total_Poli],
        "ProfitCen", 'Table'[ProfitCen],
        "EOMDate", 'Calendar'[EOMDate]
    )
)

  

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.