Create Date Table based on Min/Max of Existing Data

[bhmiller89]

I usually create a date table for my data using the usual way of defining specific beginning and end dates. I wanted to know if it is possible to create a date table based on the existing data. 

For example, I have a table that tracks service requests which all have a start date and an end date. If I want my date table to start at the MIN Service Rquest Start Date and end at the MAX Service Request Start Date, is that possible? 

[venug20 · Accepted solution]

@bhmiller89

go to ..> Model --> choose --> new table,

As per your requirement, use below formula to create table.

Table = CALENDAR(MIN('Earlier'[Date]), MAX('Earlier'[Date]) )

If it is solution to query, accept as solution... it is useful to others....

[Anonymous]

Is it possible to pull this formula created table into the "power query editor" where we can add date columns with date properties such as day of week, quarter 1, etc?  Like the screen below.

[Anonymous]

Did you ever find a solution to your question? I am trying to do the same thing myself.

[ValleyWalrus]

Try this:

Date = ADDCOLUMNS (CALENDARAUTO(),

"Year", YEAR ([Date]),

"Month", FORMAT([Date], "mmmm"),

"Month Number", MONTH([Date]),

"Quarter", FORMAT([Date], "\QQ")

)

[Anonymous]

this is awesome! Exactly what I was looking for.