between date - custom row wiht data type issue

[al1981]

I have Date table with Date column with Date format. Issue i have is that i cannot use this Date column in bellows custom formula (Expression.Error: We cannot apply operator < to types Number and Date)

What i did is duplicating column and format it into the "yyyymmdd" format as number... as those are additional steps and columns, i would like to avoid if possible... Is there a way?

if [Merged] >= 20231001 and [Merged] < 20240401 then "C1_23_24" else if [Merged] >= 20240401 and [Merged] < 20241001 then "C2_23_24" else "no cycle"

I wanted to do also this bellow but have same issue (Expression.Error: We cannot apply operator < to types Number and Date.). What would be best and most affective workarround?

let

ST = [Date],
Date1 = 01/10/2023,
Date2 = 01/04/2024,
Date3 = 01/10/2024,

Result =
if ST >= Date1 or ST < Date2 then "C1_23_24"
else
if ST>= Date2 or ST < Date3 then "C2_23_24"
else "no cycle"

in
Result

[al1981]

eeeeee 😉 missing [Date star] again 🙂 All ok!

thx!

[al1981]

hi @HotChilli 

can you please... tell me what i am missing for custom column? It`s driving me crazy .-)

if [Date start] > #date(2022, 9, 30) and < #date(2023, 4, 1) then "C1_22_23"
else
if [Date start] > #date(2023, 3, 31) and < #date(2023, 10, 1) then "C2_22_23"
else
if [Date start] > #date(2023, 9, 30) and < #date(2024, 4, 1) then "C1_23_24"
else
if [Date start] > #date(2024, 3, 31) and < #date(2024, 10, 1) then "C2_23_24"
else null

[HotChilli · Accepted solution]

Date1 = 01/10/2023 - This doesn't create a date.  Use #date()

https://learn.microsoft.com/en-us/powerquery-m/sharpdate