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?

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?

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 111 members 1,533 guests
Please welcome our newest community members: