Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculated table with all dates between two dates

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. t...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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's avatar
Phil_Seamark
Microsoft Employee
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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.