Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cgardyne
Helper I
Helper I

Dynamic Date Table based on Measures

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.

 

Capture3.JPG

 

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

 

 

3 REPLIES 3
Phil_Seamark
Employee
Employee

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

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.

 

Capture.JPG

Hi @cgardyne,

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

Thanks,
Angelia

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.