DAX to Power Query - IF Statement

[PowerUser39]

Hello,

I am trying to write the below DAX in Power Query's Custom Column but getting some errors and not getting intended results same as DAX.

DAX Code to calculate calendar years interval.

IF(

    AND(

        ISNUMBER([TEST1_YEAR]),

        IF(ISNUMBER([TEST2_YEAR]), YEAR(TODAY()) - [TEST1_YEAR], YEAR(TODAY()) - [TEST1_YEAR]) <= 6

    ),

    "Yes",

    IF(

        OR(

            AND(

                ISNUMBER([TEST3_YEAR]),

                [TEST3_YEAR] <= YEAR(TODAY())

                && [TEST3_YEAR] >= YEAR(TODAY()) - 6

            ),

            ISBLANK([TEST4])

        ),

        "Yes",

        "No"

    )

My Power Query Custom Column I have so far:

if Value.Is([TEST1_YEAR],type number) and Value.Is([TEST2],type number) and Date.Year(DateTime.LocalNow()) - [TEST1_YEAR] <= 6 then "Yes" else

if Value.Is([TEST3_YEAR],type number) and ([TEST3_YEAR] <= Date.Year(DateTime.LocalNow()) and [TEST3_YEAR] >= Date.Year(DateTime.LocalNow()) - 6) or [TEST4] = "" then "Yes" else "No"

Not sure what I am doing wrong here. Can someone please help me with correct query?

[Greg_Deckler]

@PowerUser39 I'm guessing that your errors are coming from comparing datetime to number or something else related to datetime. Can you provide sample data so I can put this into a PBIX file and test?

[PowerUser39]

Sure it is a huge dataset but it looks similar to below

TEST1	TEST2	TEST3	TEST4
2020		2020
2020		2020	5
2023	2018	2013	5
2022	2018	2005
2022		2005	5
2020	2017	2009	6

[Greg_Deckler · Accepted solution]

@PowerUser39 OK, I don't get any errors and just one difference, is this what you are seeing as well? One thing is that I don't quite understand your DAX, you seem to have a useless IF statement in there as it returns the same thing regardless (line 4). Also, be careful with using "" as you might want to change this to null in PQ. PBIX is attached.

[PowerUser39]

Thanks Greg. I inserted another condition to exclude the Null value and it worked for me.