Revised date with condition

[Anonymous]

I want to revise date against system date/time column as per mentioned the condition in below table.

System Date	Time	Condition	Revised Date
11-08-2022	18:30	If before 18:30 the revised date should be next day	12-08-2022
11-08-2022	18:30	If after 18:30 the revised date should be day after day	13-08-2022
11-08-2022	18:30	If before 18:30 and next day is holiday then revised date should be day after day	13-08-2022
11-08-2022	18:30	If after 18:30 and next day is holiday then revised date should be day after 2 days	14-08-2022

[Vijay_A_Verma · Accepted solution]

Create a list named Holidays where you store the list of Holidays and use below formula in a custom column

= Date.AddDays([System Date],1+Number.From([Time]>=#time(18,30,0))+Number.From(List.Contains(Holidays,[System Date])))

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1sNA1NFTSUTK0sDKyVIrVwRQ2NkAVNsWu2hShOhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"System Date" = _t, Time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"System Date", type date}, {"Time", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.AddDays([System Date],1+Number.From([Time]>=#time(18,30,0))+Number.From(List.Contains(Holidays,[System Date]))), type date)
in
    #"Added Custom"

Test Code for Holiday list (Give it name Holidays)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstA3NNU3MjAyUorVAfGMDaG8WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Holidays = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Holidays", type date}}),
    Holidays = #"Changed Type"[Holidays]
in
    Holidays