Skip to main content
cancel
Showing results for 
Search instead 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

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
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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