topic Re: Dax + 365 days between two dates in DAX Commands and Tips

Dax + 365 days between two dates

Malps90 — Mon, 27 Apr 2020 09:57:40 GMT

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

Re: Dax + 365 days between two dates

nandukrishnavs — Mon, 27 Apr 2020 10:41:11 GMT

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 🙂

Re: Dax + 365 days between two dates

Malps90 — Mon, 27 Apr 2020 11:32:56 GMT

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 🙂

Re: Dax + 365 days between two dates

Malps90 — Mon, 04 May 2020 09:29:55 GMT

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

Re: Dax + 365 days between two dates

nandukrishnavs — Mon, 04 May 2020 10:23:54 GMT

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 🙂

Re: Dax + 365 days between two dates

Malps90 — Mon, 04 May 2020 20:52:17 GMT

Thank you this worker exactly how I wanted it too.

Really appreciate your support.