Forum Discussion

PowerUser39's avatar
PowerUser39
Frequent Visitor
2 years ago
Solved

DAX to Power Query - IF Statement

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?

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    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.

     

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    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's avatar
      PowerUser39
      Frequent Visitor
      2 years ago

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

       

      TEST1TEST2TEST3TEST4
      2020 2020 
      2020 20205
      2023201820135
      202220182005 
      2022 20055
      2020201720096
      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        2 years ago

        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.