Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |