Dynamic Date Table based on Measures

[cgardyne]

I have a table defined by the following:

Days = CALENDAR('Date Range'[Min Date], 'Date Range'[Max Date])

The Date Range table is defined by the following:

Date Range = DATATABLE("Days", INTEGER, {{30},{60},{90}})

So that gives me a selection of either 30,60,90 where I want to use it.

Min and Max Dates are defined in the Date Range table:

Min Date = Today() - 'Date Range'[Selected Range]

and

Max Date = Today() + 1 

The Selected Range measure is defined:

Selected Range = Min('Date Range'[Days]) 

Which I think is where the problem lies.

When I select 30, 60 or 90 from Selected Range my dates don't dynamically update in the Days table and I think it's because theres no link between the two tables?

In the screenshot below when I select 60 (1) the Selected Range measure (2) updates which feeds through to the Min and Max Date table (3) but the Days Since Today table (4) still looks at the past 30 days.

So how can I create a link between the Date Range and Days tables?

[Phil_Seamark]

Hi @cgardyne

Add a calculated column to your date table like this

Date Range Filter = if(
			MAX('Days'[Date]) >= TODAY() - MIN ('Date Ranges'[ID])
			&& MAX('Days'[Date]) < TODAY() + 1
			---------------------------------------------------------
			,1
			,0
			)

Then just drag this column to the Visual, Page or Report level filters and set the filter only show days where that column is 1

Don't create a relationship between your Date Range table and your Days table

[cgardyne]

Hi @Phil_Seamark,

Thanks for your help again.

I've added a column and measure but can't quite seem to get it right.

My column is:

Date Range Filter = if('Days'[Days Since Today] >= 'Date Range'[Min Date], true, false)

 and the measure is similar but a measure:

Date Range Filter1 = if(MIN('Days'[Days Since Today]) >= 'Date Range'[Min Date], true, false)

When I add these to a table with Days[Days Since Today] as a field and then look at the filters, the column is always set to true and I can't filter down by the measure.

Screenshot below to (hopefully) explain it a bit better.

[v-huizhn-msft]

Hi @cgardyne,

The calculated column get the expected result, you must to create a measure？

Thanks,
Angelia