Forum Discussion

vtech007's avatar
vtech007
Frequent Visitor
1 year ago
Solved

Filtering Contracts based on expiring Period by months

Dear Colleagues,

 

I would like to create an IF-parameter slicer button with three categories, allowing users to filter contracts based on their expiry periods:

  1. Contracts Expiring in Less Than 3 Months (Red)

    • When selected, the table will display all contracts expiring within the next 3 months from today.

  2. Contracts Expiring in Less Than 6 Months (Yellow)

    • When selected, the table will display all contracts expiring within the next 6 months from today.

  3. Contracts Expiring in Less Than 12 Months (1 Year) (Green)

    • When selected, the table will display all contracts expiring within the next 12 months from today.

  4. Show All Contracts

    • If no button is selected, the table will display all contracts without any expiry filter.

Please attach PBIX file:

https://drive.google.com/file/d/1dNSckQUbymKccqY1TsvV83TNgoz3gdN7/view?usp=sharing 

 

see reference below.

https://www.youtube.com/watch?v=N-3bpmyuf2c  

 

Thanks for your help

Need Help
  • MattiaFratello's avatar
    MattiaFratello
    1 year ago

    Hi vtech007, have you tried to create a calculated column and then use it as a filter? 

     

    Expires In = 
VAR CurrentDate = TODAY()
VAR Expiration_Date = YourTable[Contract_End_Date]
VAR MonthsDiff = DATEDIFF(CurrentDate, Contract_End_Date, MONTH)

RETURN
    SWITCH(
        TRUE(),
        MonthsDiff <= 3, "Expires in the next 3 months",
        MonthsDiff <= 6, "Expires in the next 6 months",
        MonthsDiff <= 12, "Expires in the next 12 months"
        )

     

     

    You can then activate the select all option from your slicer

     

    If it helped you please mark it as solution

     

2 Replies

  • MattiaFratello's avatar
    MattiaFratello
    Super User
    1 year ago

    Hi vtech007, have you tried to create a calculated column and then use it as a filter? 

     

    Expires In = 
VAR CurrentDate = TODAY()
VAR Expiration_Date = YourTable[Contract_End_Date]
VAR MonthsDiff = DATEDIFF(CurrentDate, Contract_End_Date, MONTH)

RETURN
    SWITCH(
        TRUE(),
        MonthsDiff <= 3, "Expires in the next 3 months",
        MonthsDiff <= 6, "Expires in the next 6 months",
        MonthsDiff <= 12, "Expires in the next 12 months"
        )

     

     

    You can then activate the select all option from your slicer

     

    If it helped you please mark it as solution