cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smshetty
Frequent Visitor

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

 
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.
1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

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

yingyinr_0-1633071591542.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
yingyinr
Community Support
Community Support

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

yingyinr_0-1633071591542.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you so much ... Its helpful.

parry2k
Super User
Super User

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User
Super User

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





smshetty
Frequent Visitor

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors