cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NicholasBowman Frequent Visitor
Frequent Visitor

Date Formula

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?

5 REPLIES 5
Super User III
Super User III

Re: Date Formula

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 Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Microsoft v-jiascu-msft
Microsoft

Re: Date Formula

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] ) )
    )

leapyear

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
NicholasBowman Frequent Visitor
Frequent Visitor

Re: Date Formula

Hi! 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)

NicholasBowman Frequent Visitor
Frequent Visitor

Re: Date Formula

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

Microsoft v-jiascu-msft
Microsoft

Re: Date Formula

Hi @NicholasBowman,

 

Can you share a sample?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors