Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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.
@Cbutler can you try this
Filter (tbl1, tbl1[date]=calculate(max(tbl2[date]),all(tbl2)))
@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? 🙂
@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))
@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...
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
69 | |
48 | |
44 | |
18 | |
15 |