topic Re: Calculated table filter by N days in DAX Commands and Tips

Calculated table filter by N days

Cbutler — Wed, 24 Nov 2021 11:59:17 GMT

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 date (which is (today) off another calc table), then it should track back should the date filter change. But I can't seem to work out how to filter as a calculated table for the MAX date -10?

can anyone help?

Re: Calculated table filter by N days

smpa01 — Wed, 24 Nov 2021 12:13:40 GMT

Cbutler can you try this

Filter (tbl1, tbl1[date]=calculate(max(tbl2[date]),all(tbl2)))

Re: Calculated table filter by N days

Cbutler — Wed, 24 Nov 2021 12:45:51 GMT

smpa01 Thanks for replying..... Where do I add in the -10 days?

Its bringing back all the days still... I just need the last -10 days from the as at date filter

this is how I have done it in the measure that works, but I need the table version for other calcs

Greg_Deckler any ideas? 🙂

Re: Calculated table filter by N days

Greg_Deckler — Wed, 24 Nov 2021 12:45:06 GMT

Cbutler If you are trying to create a new table, maybe use CALCULATETABLE? Not sure I'm fully in sync with what you are trying to do. But something like:

Table = VAR Last_Date = MAX('Date'[Date]) RETURN CALCULATETABLE('Table',FILTER(ALL('Date'[Date]),'Date'[Date]>Last_Date - 10 && 'Date'[Date]<=Last_Date))

Re: Calculated table filter by N days

Cbutler — Wed, 24 Nov 2021 13:06:12 GMT

Greg_Deckler oh thats got me closer, but the as at date is not changing the table filter.
Its filtering from MAX table date (today) -10 so pulling data form 15-24th (no data in there for today), but when I change my filter as below...I need the table to recalculte the -10 days, so selecting 15/10/21 would filter the table to show 6/10/21 to 15/10/21...

Re: Calculated table filter by N days

Greg_Deckler — Wed, 24 Nov 2021 14:03:29 GMT

Cbutler Yeah, so if you use that as an actual table, it will not be dynamic but only caculated at load time. You will have to put it as a VAR within your other measures to make it truly dynamic. It's such a pain that measures cannot return tables. That would make things sooooo much simpler and better.

Re: Calculated table filter by N days

v-chenwuz-msft — Mon, 29 Nov 2021 06:45:14 GMT

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.

Re: Calculated table filter by N days

Cbutler — Tue, 07 Dec 2021 20:45:29 GMT

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.

Re: Calculated table filter by N days

v-chenwuz-msft — Wed, 08 Dec 2021 08:56:06 GMT

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.