dynamic date table based on min and max of date column

[domtrump]

I would like to create a date table that is based on the customer's "Sign-up Date." As I do not wish to have dates in my pivot table from before the first sign-up or after the last sign-up, I want the date table to be dynamic and grow as more dates are added. I could certainly "hard code" the start date of the table since it would coincide with the company's opening, but I need the end date to go to the last sign-up date and not show anything in the future. How can I create a Date Table that grows based on the MAX date found in the "Sign-up" column?

Note: I typically just use a linked table from Excel for my date tables in PowerPivot but this has proven problematic as I need to include ALL dates between min and max sign-up in my columns and rows even if there is no data found for them when I slice the data (i.e the resulting pivot table needs to have the same number of columns and rows regardless of the slicers applied).

[Jihwan_Kim]

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

One of many ways to achieve this is to create a calendar table by wring DAX formula.

Please check the below picture and the attached pbix file.

Calendar = 
VAR _mindate =
    MIN ( Customer[Sign-up Date] )
VAR _maxdate =
    MAX ( Customer[Sign-up Date] )
RETURN
    ADDCOLUMNS (
        CALENDAR ( _mindate, _maxdate ),
        "Year", YEAR ( [Date] ),
        "Year-Month", FORMAT ( [Date], "yyyy-mmm" ),
        "Year-Month sort", EOMONTH ( [Date], 0 )
    )

[domtrump]

Looks like that would do it, but where do I place this formula?

[domtrump]

Does this just make a virtual table that you can use in formulas? How can I make a physical table that exhibits this behavior? Or is using a virtual table the way to go? Either way, I'm not sure where to place this formula in my PP Data Model.