Forum Discussion

cgardyne's avatar
cgardyne
Helper I
9 years ago

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", INTE...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

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's avatar
    cgardyne
    Helper I
    9 years ago

    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's avatar
      v-huizhn-msft
      Microsoft Employee
      9 years ago

      Hi cgardyne,

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

      Thanks,
      Angelia