Forum Discussion
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 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?
8 Replies
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 filterthis is how I have done it in the measure that works, but I need the table version for other calcs
Greg_Deckler any ideas? 🙂
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))
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.
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.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.
