DATEVALUE - Combining 2 sections of text to make a date

[Anonymous]

CONTEXT:  I work in a company who track Holiday / Leave entitlements per staff by their birth month, rather than start of financial yr / Jan-Dec. IE, Staff A has birth month of August, so his "Entitlement yr" this year is Aug18 - July 31st 19. I am tasked with recreating a report I made in Excel in power bi, which we've only just started using and I'm new to. 

We sum, per month, all holiday / Leave bookings made by staff and compare against their Monthly accrual which is their Annual entitlement / 12. As you can imagine, looking at a 12 month previous date range means you cross over 2 entitlement periods for most people.

Important to track which entitlement year a booking came out of, so in Excel I made a column with a formula which gives the value as Mmm-YY for what is their Current entitlement yr based on comparing their Birth month to todays date. For those who know excel, this formula is below. It uses DATEVALUE to turn a text combination of Birth MONTH and today's / a year ago today's YEAR value into a valid date format.

IF(VALUE(MONTH(TODAY()))>=MONTH(B2),DATEVALUE(TEXT(B2,"Mmm")&"-"&TEXT(TODAY(),"yy")),DATEVALUE(TEXT(B2,"Mmm")&"-"&TEXT(TODAY()-365,"yy")))

I'm trying to recreate the formula in DAX but have limited knowledge. I keep getting an error relating to converting text into a date, which reads: "Cannot convert value '-1905' of type text to type Date". Please see DAX below:

Current Birthmonth =
if(MONTH(today())>=month('Staff Info'[Birth Date]),
DATEVALUE(format(month('Staff Info'[Birth Date]),"Mmm") & "-" & format(year(TODAY()),"yyyy")),
DATEVALUE(format(month('Staff Info'[Birth Date]),"Mmm") & "-" & format(year(TODAY())-365,"yyyy")))

Any help would be appreciated!!! 

Cheers,

[v-piga-msft · Accepted solution]

Hi @Anonymous,

Please try the formula below.

Current Birthmonth = 
IF (
    MONTH ( TODAY () ) >= MONTH ( 'Staff Info'[Birth_Date] ),
    DATEVALUE (
        FORMAT ( DATE ( 2018, MONTH ( 'Staff Info'[Birth_Date] ), 1 ), "Mmm" ) & "-"
            & FORMAT ( DATE ( YEAR ( TODAY () ), 1, 1 ), "yyyy" )
    ),
    DATEVALUE (
        FORMAT ( DATE ( 2018, MONTH ( 'Staff Info'[Birth_Date] ), 1 ), "Mmm" ) & "-"
            & FORMAT ( DATE ( YEAR ( TODAY () ), 1, 1 ) - 365, "yyyy" )
    )
)

Then you will get the output below.

If you still need help, please share some data sample and your desired output so that we could help further on it.

Best Regards,

Cherry

[Anonymous]

That has indeed worked! Thank you very much!

Out of curoisity - I see the main change is adding a ,1) after the Format( Date ( 2018, Month ('staff info[birth date]) part. What does this 1 do exactly as I did not see that section on the Syntax guide as I was typing it out.

Thanks once again.