Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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.
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.
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!
Hi Greg
You advice was very helpfull.
I use DATE inside DATESINPERIOD
Amdi Silword
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.
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |