Forum Discussion

Cbutler's avatar
Cbutler
Icon for Helper III rankHelper III
4 years ago

Calculated table filter by N days

Help!  I am trying to create a calculated table that filters by a dynamic date to pull back the results for the last n or 10 days.  So I need a calculated table that is written to show the MAX ...
DAX
Filter
summerize
Table
v-chenwuz-msft's avatar
v-chenwuz-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi Cbutler ,

 

The table in the Datacan not be a dynamic table unless you use power query editor. But in visual table ok.

 

In visual table, you can create a measure like the following to show the last n days.

IF in N days =
VAR _Nday =
    SELECTEDVALUE( 'N days Parameter'[N days Parameter] )
VAR _StartDate =
    TODAY() - _Nday
RETURN
    IF(
        SELECTEDVALUE( 'Table'[Date] ) >= _StartDate
            && SELECTEDVALUE( 'Table'[Date] ) <= TODAY(),
        1,
        0
    )

 

Before this , you should create a parameter named N days Parameter via what if new parameter 

Then, put the measure IF in N days into the filters on this visual and set item is 1 as the screenshot:

 

And i put my example pbix file in the end you can refer.

 

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • Cbutler's avatar
    Cbutler
    Icon for Helper III rankHelper III
    4 years ago

    Hi v-chenwuz-msft , thanks for this, unfortunatey I do not wish the number of days to be dynamic, but the MAX date must be dynamic to change what is pulled in to the calculated table. 

    eg. Max date is "today", so my table shows 07/12/21 and the 10 days before (data in table is 27/11/2021 to 07/12/21) however I want to view data "as at" 11/11/21, so I want to use a slicer to change the MAX date to 11/11/21 so the data in the calculated table shows data from 01/11/21 to 11/11/21. 

    Hope that makes sense. I have a work around with measures, but this makes the report perfrmance very poor! and when I tried to load it earlier was getting a memory error and the visuals could not display 😞

    If I can not find a solution I will just have to have it as a fixed "today" date and not allow users to look back at the historical data. 

    • v-chenwuz-msft's avatar
      v-chenwuz-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi Cbutler ,

       

      I probably understand what you mean. Select a day, and the selected day is the maximum date within ten days. What needs to be displayed is the data for these ten days.

       

      Before creating a measure, first create a table through the following code to achieve.

      Max date for slicer = CALENDAR(MIN('Table'[Date]),TODAY())

       

      Then the code for measure:

      IF in N days =
VAR _Nday = 10
VAR _EndDate =
    IF(
        SELECTEDVALUE( 'Max date for slicer'[Date] ) = BLANK(),
        TODAY(),
        SELECTEDVALUE( 'Max date for slicer'[Date] )
    )
VAR _StartDate = _EndDate - _Nday
RETURN
    IF(
        SELECTEDVALUE( 'Table'[Date] ) >= _StartDate
            && SELECTEDVALUE( 'Table'[Date] ) <= _EndDate,
        1,
        0
    )

       

      Drag measure into Filters on this visula and set it show items is 1. Create a slicer for user to select the MAX date. the values of slicer come from new table 'Max date for slicer'. Rest assured, this 'Max date for slicer' will change with the refresh of the data.

       

      Best Regards

      Community Support Team _ chenwu zhu

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.