Calculated table with all dates between two dates

[Anonymous]

Hi All,

I am trying to create a calculated table that lists all tickets and a record for each day that it was opened based on the Start Date and End Date columns. If the End Date is blank (i.e. the ticket is still open), I want it to list all dates from the start date to today's date.

For example, today's date is 5/15/2018. I would want the data from this table:

TicketStart DateEnd Date

1	5/11/2018	5/14/2018
2	5/11/2018

To calculate into this:

TicketDate

1	5/11/2018
1	5/12/2018
1	5/13/2018
1	5/14/2018
2	5/11/2018
2	5/12/2018
2	5/13/2018
2	5/14/2018
2	5/15/2018

I can't quite get there. Any ideas?

[Anonymous · Accepted solution]

FYI - I pieced together some google searches and was able to do what I needed in the query editor:

1. Duplicated my source table
2. Created a custom column with today's date using the following: DateTime.LocalNow() as datetime
3. Changed that column to a date type instead of datetime
4. Added another custom column using: if [Closed Date] = null then { Number.From([Opened Date])..Number.From([Today]) } else { Number.From([Opened Date])..Number.From([Closed Date]) }
5. Expanded the column to rows
6. Changed to a Date type
7. Removed all the remaining columns I didn't need.

[Phil_Seamark]

Hi @Anonymous

I think this calculated table gets close.  I have attached a PBIX file.

Table 2 = 
SELECTCOLUMNS(
    GENERATE(
            'Table1',
            FILTER(
                CALENDAR(MIN('Table1'[StartDate]),MAX('Table1'[DateEnd]))
                ,[Date]>=[StartDate] && [Date] <= IF(NOT IsBlank([DateEnd]),[DateEnd],MAX('Table1'[DateEnd])
                )
            )
       ),"ID",[ID],"TicketDate",[Date])

[Anonymous]

@Phil_Seamark is there a way to do this but look at months so rather looking at every day date look at every month date?

[Anonymous]

if there a way to do this but look at months so rather looking at every day date look at every month date?

[Anonymous]

Thanks Phil, however it didn't list today's date for the ID 2 record, which is the one that didn't have an end date. I was able to piece together a solution from some googling I did and posted it as a solution for reference.