cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kleetus51 Member
Member

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?

1 ACCEPTED SOLUTION

Accepted Solutions
kleetus51 Member
Member

Re: Calculated table with all dates between two dates

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 5
Phil_Seamark Super Contributor
Super Contributor

Re: Calculated table with all dates between two dates

Hi @kleetus51

 

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])

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

kleetus51 Member
Member

Re: Calculated table with all dates between two dates

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

Re: Calculated table with all dates between two dates

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.

dataman123 Member
Member

Re: Calculated table with all dates between two dates

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

Highlighted
dataman123 Member
Member

Re: Calculated table with all dates between two dates

@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?