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 is for my apraisal dashbaord. 

 

Many thanks 

  • 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     🙂

5 Replies

  • 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         🙂