Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I have this table
I'm trying to reduce Date1 by the Month column and return the results in Date2.
I'm using the formula;
=dateadd(Resign_Append[Date1],-Resign_Append[Month],MONTH)
I have no idea why I'm getting values in some rows of Date2 and not in others (as above).
Any ideas?
Thanks in advance
Solved! Go to Solution.
@PMF99 ,
Here is the formula in DAX:
New Date DAX = VAR _year = YEAR ( Resign_Append[Date1] ) VAR _month = MONTH ( Resign_Append[Date1] ) VAR _day = DAY ( Resign_Append[Date1] ) VAR _monthToSubstract = Resign_Append[Month] RETURN DATE ( _year, _month - _monthToSubstract, _day )
DATEADD is a time intelligence function that returns a table, so you will get the incorrect results. You use Date.AddMonths() function in Power Query to substract the month from your date.
Thanks NickNg.
Any idea how do perform this calculation in DAX as I have multiple queries in my model?
@PMF99 ,
Here is the formula in DAX:
New Date DAX = VAR _year = YEAR ( Resign_Append[Date1] ) VAR _month = MONTH ( Resign_Append[Date1] ) VAR _day = DAY ( Resign_Append[Date1] ) VAR _monthToSubstract = Resign_Append[Month] RETURN DATE ( _year, _month - _monthToSubstract, _day )
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |