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. 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

15/11/20185/14/2018
25/11/2018 

 

To calculate into this:

TicketDate

15/11/2018
15/12/2018
15/13/2018
15/14/2018
25/11/2018
25/12/2018
25/13/2018
25/14/2018
25/15/2018

 

I can't quite get there. Any ideas?

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.

5 Replies

  • 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
    Anonymous
    Not applicable
    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.