Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
antel1
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
Greg_Deckler
Super User
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...

View solution in original post

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

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

You advice was very helpfull.

I use DATE inside DATESINPERIOD

 

Amdi Silword

 

DATESINPERIOD.PNG

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.