Reshape data

[Anonymous]

Hello folks,

I'm struggling with some data I need to "shape". Basically I have a table with a single row for each support ticket that is raised. The row has a "Date Created" field and a "Date Resolved" field. To allow me to use the data more easily I'd like to be able to have a duplicate row for each day that the ticket remains open.

The original table is:

Ticket Number   Date Created   Date Resolved   Agent Name

135118                01/03/2017     05/03/2017       Michael

135119                09/03/2017     10/03/2017       Stuart

The new table should be:

Ticket Number   Date Created   Date Resolved   Agent Name

135118                01/03/2017     05/03/2017       Michael

135118                01/03/2017     05/03/2017       Michael

135118                01/03/2017     05/03/2017       Michael

135118                01/03/2017     05/03/2017       Michael

135118                01/03/2017     05/03/2017       Michael

135119                09/03/2017     10/03/2017       Stuart

135119                09/03/2017     10/03/2017       Stuart

Has anyone got any ideas on the best way to achieve this?

Thanks!

Bailey

[Phil_Seamark · Accepted solution]

Hi @Anonymous,

You could try creating a New Table in DAX and use the following code

Table Expanded = FILTER(
                    CROSSJOIN('Table',CALENDARAUTO()),
                    [Date]>='Table'[Date Created] 
                    && [Date] <='Table'[Date Resolved]
                    )

[Anonymous]

@Phil_Seamark

Thanks Phil! Perfect! It almost looks too simple but it works perfectly. Thanks again for taking the time to provide me with an answer.