Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Cbutler
Helper III
Helper III

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? 

Cbutler_0-1637754913557.png

 

can anyone help? 

 

8 REPLIES 8
v-chenwuz-msft
Community Support
Community Support

Hi @Cbutler ,

 

The table in the Datavchenwuzmsft_1-1638167685798.pngcan 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 

vchenwuzmsft_2-1638168051541.png

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

vchenwuzmsft_3-1638168185612.png

 

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.

smpa01
Super User
Super User

@Cbutler can you try this

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@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 

Cbutler_0-1637757942529.png

 

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

Cbutler_0-1637756664233.png

@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))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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_0-1637759006510.png

 

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors