cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

Subtract Certain Number of Months from a Date Column and Return the Max Date

Hi,

I'm trying to do what should be a very simply calculation, however everything I try to do seems to give me an error. All I want to do is find the last date in a column and subtract 3 months from this date.

Here are a few of my attempts

```test =
VAR BookingUpperLimit = DATEADD(MAX(CaseHistory[EndTime]), -3, MONTH)
RETURN
BookingUpperLimit```
```test =
VAR temptable = DATEADD(CaseHistory[EndTime],-3,MONTH)
Var BookingUpperLimit = MAXX(temptable,)
RETURN
BookingUpperLimit```

The second attempt obviously is incomplete as I can't retrieve the single column from the table 'temptable'. The first attempts gives the error "A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

How can I calculate the date I specified above?

1 ACCEPTED SOLUTION
Super User

Nope, DATEADD is an awful, awful function. It only accepts a table of dates. You could do it like this for simple cases:

```Measure 6 =
VAR __max = MAXX('Calendar',[Date])
VAR __year = YEAR(__max)
VAR __day = DAY(__max)
VAR __month = MONTH(__max)
RETURN
DATE(__year,__month-3,__day)```

Might have to check for boundary conditions (year rollover). Stay away from DATEADD it will only destroy your morale.

@ 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...
6 REPLIES 6
Super User

Nope, DATEADD is an awful, awful function. It only accepts a table of dates. You could do it like this for simple cases:

```Measure 6 =
VAR __max = MAXX('Calendar',[Date])
VAR __year = YEAR(__max)
VAR __day = DAY(__max)
VAR __month = MONTH(__max)
RETURN
DATE(__year,__month-3,__day)```

Might have to check for boundary conditions (year rollover). Stay away from DATEADD it will only destroy your morale.

@ 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...
Anonymous
Not applicable

Hi

Im experienceing a problem now where I go month - 1 and it seems to be only subtracting 30 days. For instance on my slicer I select July, and I'm expecting my measure to retun June, but it is only returning 2021/07/01.

Have you encounter this before?

Regards

Helper I

Adding for beginners that this also works with the function TODAY() (and as @basel777  mentions it accounts for the year rollover).  In the example below I'm limiting the bounds of two measure to the last 3 months in relation to the current month:

``````m_Missing%Last3Months =
VAR date1 = TODAY()
VAR _year = YEAR(date1)
VAR _month = MONTH(date1)
VAR _day = DAY(date1)

return
CALCULATE([m_MissingCount]/[m_ExpectedCount],
FILTER(WorkOrder,WorkOrder[serviceDT]>DATE(_year,_month-3,_day) &&
WorkOrder[serviceDT]<DATE(_year,_month,1)
)
)``````

Thanks @Greg_Deckler  for the solution, this saved me quite a bit of time!!

@Greg_Deckler I thought I would add that this solution adapts to boundary conditions, so if someone has a date of 01/01/2020, and subtracts a day, the updated date would 12/31/2019... Which is freaking sweet! 🙂 Thanks again for helping us out!

Anonymous
Not applicable

Hi Greg

I use DATE inside DATESINPERIOD

Amdi Silword

Frequent Visitor

Thanks for the help. It's ridiculous how limited the date functions are in DAX. I have trouble whenever I want to anything involving dates that isn't a simple slicer.

Announcements

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors