How to check if 29th of February is between start date and end date

[a4]

I have a table (salesorderdetails) which has two column startdate and enddate. I want to find if 29th of February falls between startdate column and enddate column.

If 29th of February falls between startdate and endadate output will be 366 else 365.

Find find below the screenshot of the table for better understanding.

As the startdate is 20-02-19 and enddate is 20-02-20 so 29th of February is not between startdate and enddate then we will have toi display 365 and if 29th of February is between startdate and endate display 366.

Kind Regards

Amoit Kumar

[dax · Accepted solution]

Hi @a4 , 

You could try below M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ30jcyUNIBso0MIOxYHaiMsb4hTALEhIpbwMWhSiDiYN1Q9YZgZmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [start = _t, end = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"start", type date}, {"end", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if (Number.Mod( Date.Year([start]),4)=0 and Date.Month([start])*100+Date.Day([start])<229 ) or ( Number.Mod( Date.Year([end]),4)=0 and Date.Month([end])*100+Date.Day([end])>229)
    then 366 else 365),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each [end]-[start] )
in
    #"Added Custom1"

You could use this expression in M code

But I think [end]-[start] wil be more easier

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[a4]

Hi Zoe,

This logic will fail if startdate is 2nd of February 2020 and enddate is 10th of February 2020 or if startdate is 1st of january 2020 and enddate is 15th of February 2020

Please suggest a solution.

Kind Regards

Amit Kumar

[dax]

Hi @a4 , 

You could try below M code in custom column to see whether it work or not

if  ((try #date(Date.Year([start]),2,29) otherwise #date(1900,1,1)) >[start] and (try #date(Date.Year([start]),2,29) otherwise #date(1900,1,1))<[end]) or

((try #date(Date.Year([end]),2,29) otherwise #date(1900,1,1)) >[start] and (try #date(Date.Year([end]),2,29) otherwise #date(1900,1,1))<[end] )

then 366 else 365

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[a4]

Hi Zoe,

Thanks for the solution, it worked perfectly.

Kind Regards

Amit Kumar

[amitchandak]

@a4 , Not sure I got the issue. But you can use datediff >

datediff([start date], [end date], day)

[a4]

Hi Amit,

Let me just clear the problem statement.....

If 29th of Februaray is between startdate and enddate then I want to create a calculated and display 366 and if 29th of February does not fall in between startdate and enddate I want to dispaly 365 in the calculated column.

For better understanding please refer the table in the first post.

Sorry for the bad expalnation.

Kind Regards

Amit Kumar