I am trying to create a measure that will slice a date by 30, 60, and 90 days from current day. I have an expiration date column and I am trying to create a slicer that lets me show accounts that will be expiring in the next 30, 60, and 90 days from the current day. Any ideas?

HI, Anonymous You could try this way as below: Step1: Add a simple table that only contains 30,60,90 value for slicer. For example: In the example, I use a simple data sample Step2: Then add this conditional in your formula Measure = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date]<=TODAY()+SELECTEDVALUE(Days[Days after],MAX(Days[Days after])))) Result: here is sample pbix file, please try it. Best Regards, Lin

30, 60, 90 day filter | Microsoft Fabric Community

5 Replies

Greg_Deckler
Community Champion
7 years ago
If the data is updated everyday the simplest method is to create a calculated column that contains the designation 30, 60, 90 days. Then you can use this calculated column in a slicer.

If you are in the situation where you need this to be 100% dynamic, then you need to use the Disconnected Table Trick. As this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563
- Anonymous
  Not applicable
  7 years ago
  I am not sure how to create such a table.
  
  update: I went ahead and created a new table with a period that I was able to add a relationship to my existing calendar using this code:
  
  DatePeriod =
  UNION (
  ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today(),today()+30) ), 'Date'[Date]),"Period","Next 30 Days") ,
  ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today(),today()+60) ), 'Date'[Date]),"Period","Next 60 Days") ,
  ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today(),today()+90) ), 'Date'[Date]),"Period","Next 90 Days") )
  - v-lili6-msft
    Community Support
    7 years ago
    HI, Anonymous
    
    You could try this way as below:
    
    Step1:
    
    Add a simple table that only contains 30,60,90 value for slicer.
    
    For example:
    
    In the example, I use a simple data sample
    
    Step2:
    
    Then add this conditional in your formula
    
    Measure = CALCULATE(SUM('Table'[Value]),
    FILTER('Table','Table'[Date]<=TODAY()+SELECTEDVALUE(Days[Days after],MAX(Days[Days after]))))
    
    Result:
    
    here is sample pbix file, please try it.
    
    Best Regards,
    
    Lin
    demo.pbix49 KB
Dyuti_M9
Frequent Visitor
6 months ago
Hi dipaklunawat , v-lili6-msft ,
You can find this method helpful.
Suppose you create a table as "Account Data" with "AccountID","Account_holder"
and "Expiration_Date"
Account Data =
DATATABLE(
"Account_ID",
STRING,
"Account_holder",
STRING,
"Expiring Date",
DATETIME,
{
{"A1","Emily","2026/4/4"},
{"A2","Jake","2026/6/22"},
{"A3","Mike","2026/5/24"},
{"A4","Sharon","2026/6/4"},
{"A5","Peter","2026/5/20"},
{"A6","Susan","2026/9/23"},
{"A7","Liza","2026/6/4"}
}
)
You can create a Calender table(Optional).
1. Add a calculated column to the Account Data table as "Will Expire In (Days)"
2. Write a Measure:
About to expire in =
Var Daysleft = 'Account Data'[Expiring Date]-TODAY()
Var Period =
IF(
Daysleft<=30,"In 30 days",
IF(
Daysleft>30 && Daysleft<=60,"In 60 Days",
IF(
Daysleft>60 && Daysleft<=90,"In 90 Days",

"Above 90"
)
)
)
RETURN
Period
3. Make sure the data type is selected as text else it will show an error.
4. By now the column will categorize days as "In 30 Days","In 60 days" or "In 90 days"
5. Go to Report view.
6. Choose a slicer as you prefer and add "Will Expire In (Days)" column in value field.
7. Select Matrix visual, Add Account_ID in the row feild and Expiring Date in the value field.
8. For clear visualization add a filter to the page and bring "Will Expire In (Days)" to the data tab and deselect "Above 90 " option.

Forum Discussion

30, 60, 90 day filter