topic Re: dynamic date table based on min and max of date column in DAX Commands and Tips

dynamic date table based on min and max of date column

domtrump — Mon, 29 May 2023 19:08:11 GMT

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

Re: dynamic date table based on min and max of date column

Jihwan_Kim — Tue, 30 May 2023 03:38:26 GMT

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

Re: dynamic date table based on min and max of date column

domtrump — Sun, 25 Jun 2023 20:39:55 GMT

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

Re: dynamic date table based on min and max of date column

domtrump — Thu, 29 Jun 2023 14:19:31 GMT

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.