Forum Discussion

Malps90's avatar
Malps90
Icon for Helper I rankHelper I
6 years ago
Solved

Dax + 365 days between two dates

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...
  • nandukrishnavs's avatar
    nandukrishnavs
    6 years ago

    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's avatar
    nandukrishnavs
    6 years ago

    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     🙂

nandukrishnavs's avatar
nandukrishnavs
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
    Malps90
    Icon for Helper I rankHelper I
    6 years ago

    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 🙂

  • Malps90's avatar
    Malps90
    Icon for Helper I rankHelper I
    6 years ago

    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's avatar
      nandukrishnavs
      Icon for Community Champion rankCommunity Champion
      6 years ago

      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's avatar
        Malps90
        Icon for Helper I rankHelper I
        6 years ago

        Thank you this worker exactly how I wanted it too.

         

        Really appreciate your support.