How to calculate 30 days from end date in the date slicer

[smshetty]

How to calculate 30 days from end date in the date slicer

the below Dax formula gives me the information from today to earlier 30 days.

IF(DATEDIFF('Calculation'[Date],TODAY(),day)<=30,"30 days","30 days beyond")

How can tranform this formula to refer to end date selected to earlier 30 days. I tried max( date) ,but that goes till the time information available. i want it to based on user selected end date to prior 30 days.

Need assistance.

[v-yiruan-msft · Accepted solution]

Hi @smshetty ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Create a date dimension table 

2. Use the Date field of date dimension table as slicer option

3. Create a measure as below to get the status

Measure = 
VAR _maxseldate =
    MAX ( 'Date'[Date] )
RETURN
    IF (
        DATEDIFF ( SELECTEDVALUE ( 'Calculation'[Date] ), _maxseldate, DAY ) <= 30,
        "30 days",
        "30 days beyond"
    )

Best Regards

[CervM46]

I'm trying to do the same thing except i want the user to select from the slicer, 60,90,120 days.  The selection will tell them what contracts are coming up for assesment based on the End Date.  In excel i just say end Date-60. But i don't know how to create a measure that can be put into a slicer where it can determine what contracts are ready to be reviewed for renewal.  If the contractor knows what is coming up for assesment then they can send out their reminders prior to that to get the ball rolling for coordination. 

[smshetty]

Thank you so much ... Its helpful.

[parry2k]

/@smshetty are you adding it as a measure or a column

[parry2k]

@smshetty you have to add it as  a measure then use MAX of date instead of TODAY()

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[smshetty]

Max function goes to max available date which can be today too.. I want it to use the end date selected by the use in the date slicer