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

[antel1]

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?

[Greg_Deckler · Accepted solution]

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.

[Anonymous]

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

[HansTheEnforcer]

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!!

[basel777]

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

Hi Greg

You advice was very helpfull.

I use DATE inside DATESINPERIOD

Amdi Silword

[antel1]

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.