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.
Currently trying to convert a person's birth date to the same month and day in the current year. I have used the date formula as such: Birth Date This Year = DATE(YEAR(today()),MONTH(contacts[Birth Date]),DAY(contacts[Birth Date])). The problem seems to be in the day component as if i substitute that argument with a normal integer then the formula pulls through the correct date and month. I am just a little confused why the month component of the formula would work but the same logic in the day component is causing an error?
Hi @NicholasBowman,
I guess there could be a date like 2004/2/29 while we don't have 2/29 in the current year. Please try the formula below.
Column 2 = VAR yearNow = YEAR ( TODAY () ) VAR isLeapNow = IF ( MOD ( yearNow, 4 ) = 0 && MOD ( yearNow, 100 ) <> 0, 1, IF ( MOD ( yearNow, 400 ) = 0, 1, 0 ) ) RETURN IF ( FORMAT ( [Date], "MMDD" ) = "0229" && isLeapNow <> 1, DATE ( 2100, 1, 1 ), //change this part to what you like DATE ( yearNow, MONTH ( [Date] ), DAY ( [Date] ) ) )
Best Regards,
Dale
Thanks, i did try this but ended up with the same error response. Tried to test it another way by using an if function to return a different result if the DAY(date) = 29, but that also resulted in the same error
Hi @NicholasBowman,
Can you share a sample?
Best Regards,
Dale
Hi @NicholasBowman,
The formula you are using should be correct, is the format of the Birth Date correct? Is it configured in your model as a date?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi! Thanks for your response. The formatting is correct - if i simply use the DAY function then it correctly extracts the day, but when i insert that in to the overall date function if get the error that 'an argument of function 'date' has the wrong data type or the result is too large or too small'. I am using the same column to determine the birthday month and day, so am struggling to understand why within the date formula there is suddenly an error (it works perfectly in the month position of the formula)
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |