Dax + 365 days between two dates

[Malps90]

Hi, 

i am having a problem writing DAX what i am looking to do is if (date colunm1) has a date then return date + 365 days if Blank then use ( date column 2) and return date + 365 days. 

This is for my apraisal dashbaord. 

Many thanks 

[nandukrishnavs · Accepted solution]

@Malps90 

Since this is a data preparation activity I would recommend you to implement the same in Edit Query.

Try the below example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ11DUyMDJQ0lFSitWJVgLSRjCh2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}, {"Column2", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each if [Column1]=null then Date.AddDays([Column2],365) else Date.AddDays([Column1],365))
in
    #"Added Custom"

Below is the DAX logic

Date1 = 
var d1= CALCULATE(SELECTEDVALUE('Table'[Column1]))
var d2= CALCULATE(SELECTEDVALUE('Table'[Column2]))
var result= IF(ISBLANK(d1),d2+365,d1+365)
return result

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[nandukrishnavs · Accepted solution]

@Malps90 

Since this is a data preparation activity I would recommend you to implement the same in Edit Query.

Try the below example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ11DUyMDJQ0lFSitWJVgLSRjCh2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}, {"Column2", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each if [Column1]=null then Date.AddDays([Column2],365) else Date.AddDays([Column1],365))
in
    #"Added Custom"

Below is the DAX logic

Date1 = 
var d1= CALCULATE(SELECTEDVALUE('Table'[Column1]))
var d2= CALCULATE(SELECTEDVALUE('Table'[Column2]))
var result= IF(ISBLANK(d1),d2+365,d1+365)
return result

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Malps90]

Will this formula allow me to add a third date column in i have realised i need a 3rd column if the start date is blank then +365 days from the created date.

thanks sorry to be a pain i tried to amend the forumla with the third column and thowing back an error 

Next Review =

var d1= CALCULATE(SELECTEDVALUE('Mi-Review Report'[Last Review]))

var d2= CALCULATE(SELECTEDVALUE('Mi-Review Report'[Start Date]))

var d3= CALCULATE(SELECTEDVALUE('Mi-Review Report'[Create Date]))

var result= IF(OR(ISBLANK(d1),d2+365,d3+365,d1+365))

return result

[nandukrishnavs · Accepted solution]

@Malps90 

Try this

Next Review =
VAR d1 =
    CALCULATE (
        SELECTEDVALUE ( 'Mi-Review Report'[Last Review] )
    )
VAR d2 =
    CALCULATE (
        SELECTEDVALUE ( 'Mi-Review Report'[Start Date] )
    )
VAR d3 =
    CALCULATE (
        SELECTEDVALUE ( 'Mi-Review Report'[Create Date] )
    )
VAR result =
    IF (
        ISBLANK ( d1 ),
        IF (
            ISBLANK ( d2 ),
            d3 + 365,
            d2 + 365
        ),
        d1 + 365
    )
RETURN
    result

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Malps90]

Thank you this worker exactly how I wanted it too.

Really appreciate your support.

[Malps90]

Thank you very much this worked exactly how i wanted it too. 

im new to this platform so trying to work everything out i know what i want it to do but just cannot write it 🙂