Help with Understanding Date Table Configuration

[SMW]

Hello,

I have been working on trying to find a solution to creating a method to continuously add onto static dates in an excel sheet to keep the dates relavant. I want the dataset to shift altogether past and future dates to maintain the same range of dates but moved forward to maintain the time intelligence functions currently used. IE A date field would progressively increase each day through an automatic process in PBI. So far what I thought was the solution is this: 

Date Table =

VAR _MinDate = MIN('Table'[Start Date])

VAR _MaxDate = MAX('Table'[Start Date])

VAR _StartDate = DATEADD(_MinDate, 10, DAY)

VAR _EndDate = DATEADD(_MaxDate, 10, DAY)

return

ADDCOLUMNS(

CALENDAR(

_StartDate, _EndDate),

"Year",YEAR([Date]),.....continues to create an entire date table. This table is created using DAX NOT PowerQuery

My understanding is that this would add 10 days to all the dates in the model. I can then continue to adjust the number of days added to maintain an up to date dates without manually adjusting all of them in the static excel file. However, it seems that I am unable to use a variable in the Dateadd function. I receive an error stating the dateadd function requires a column input. I tried a previous solution where I just created a variable: 

VAR _MinDate = MIN('Table'[Start Date]) + 10

VAR _MaxDate = MAX('Table'[Start Date]) +10 

and then used these variables in the parameters of the calender creation. This seemed to work with no errors but did not seem to produce the intended effect throughout the model. 

Does anyone have a better working solution to accomplish something like this to adjust ALL the date columns that are connected to the date table all at once through the date creation table? 

Thank you.

[amitchandak]

@SMW , Date add needs continuous dates , so the first one did not work.

Table is static in nature, So your min/max are based on overall value, not slicer values. So the second table will work but will be static

If you want to slicer value, you have to use measure on a var table in measure can be dynamic

[SMW]

@amitchandak Thank you for the explaination. I'm ok with it being 'static' as long as I can continue to change the number of days added in one place. IE each time I want to use the dashboard I can change the 10 to 25 and it will appear as recent relevant data throughout the model. 

I am confused though as to what you mean by making a measure to create a slicer value? 

[amitchandak]

@SMW , Assume you want dashboard to show +-10 days of data .

In my date table I would just create a new field

New column =

VAR _MinDate = MIN('Table'[Start Date]) + 10
VAR _MaxDate = MAX('Table'[Start Date]) +10

return

if([Date] <= _maxDate && [Date] >=_MinDate, 1,0)

You can some better names than 1 or 0 and can use that as a slicer or filter to control the page value

For measure  , you can have a measure like this and use it as a visual level filter.  or build all your measures like the below.

New column =

VAR _MinDate = Minx(allselected('Table'), 'Table'[Start Date]) + 10
VAR _MaxDate = MAXx(allselected('Table'), ''Table'[Start Date]) +10

return

if(Max(Date[Date]) <= _maxDate && [Date] >=_MinDate, 1,0)

Not these dates(measure) can be dynamic based on slicer selection 

[SMW]

Thank you. I understand that explaination but I don't think that answeres my true problem. I am trying to create a solution to always be adding days to my static data so that I do not have to go into the raw data to update. IE right now the data source has a range of 3 years some dates are for completed tasks(past), others are for future tasks(future dates) that need complete and other are for what needs complete this week(current) Therefore I need past, present and future dates to all move along to be relevant. For example, in another couple months many of my date intelligence measures will fall off as those dates age out of being current. So what I am trying to do is through the date table add days to all the dates so that when I use the dashboard in another couple months those dates that are technically now in the past, will add days back automatically and make them seem like they are current or just happened last week. That is why I was trying to add to the date table so it would reflect in all the dates throughout the model as I have many dates assocaited. Therefore adding 10 days to the date table will automatically add 10 days to another date column that is connected to the dates table. Is that possible?