Help Converting Excel Formula to DAX

[CiaraCaryl]

Could someone please help me convert this excel formula to DAX? I was thinking I could use the Switch function but I am having trouble formatting it correctly.

IF([@InvoiceDate]>DATE(2020,12,31),
(IF([@[ProductType_Sub3]]="monthly",([@ExtensionAmt]*72),
(IF([@[ProductType_Sub3]]="yearly",([@ExtensionAmt]*6),
(IF([@[ProductType_Sub3]]="5 Year",([@ExtensionAmt]/5)*6,
(IF([@[ProductType_Sub3]]="4 Year",([@ExtensionAmt]/4)*6,
(IF([@[ProductType_Sub3]]="3 Year",([@ExtensionAmt]/3)*6,
(IF([@[ProductType_Sub3]]="2 Year",([@ExtensionAmt]/2)*6,)))))))))))),
(IF([@[ProductType_Sub3]]="monthly",([@ExtensionAmt]*36),
(IF([@[ProductType_Sub3]]="yearly",([@ExtensionAmt]*3),"")))))

[v-yajiewan-msft · Accepted solution]

Hi @CiaraCaryl , hello, Brightsider & Greg_Deckler , thank you for your promot reply!

We could also use the following measure to meet your requirement:

Result = 
SWITCH(
    TRUE(),
    SELECTEDVALUE('Table'[InvoiceDate])> DATE(2020, 12, 31),
        SWITCH(
            TRUE(),
            SELECTEDVALUE('Table'[ProductType_Sub3]) = "monthly", SELECTEDVALUE('Table'[ExtensionAmt]) * 72,
            SELECTEDVALUE('Table'[ProductType_Sub3]) = "yearly", SELECTEDVALUE('Table'[ExtensionAmt]) * 6,
            SELECTEDVALUE('Table'[ProductType_Sub3]) = "5 Year", (SELECTEDVALUE('Table'[ExtensionAmt]) / 5) * 6,
            SELECTEDVALUE('Table'[ProductType_Sub3]) = "4 Year", (SELECTEDVALUE('Table'[ExtensionAmt]) / 4) * 6,
            SELECTEDVALUE('Table'[ProductType_Sub3])= "3 Year", (SELECTEDVALUE('Table'[ExtensionAmt]) / 3) * 6,
            SELECTEDVALUE('Table'[ProductType_Sub3]) = "2 Year", (SELECTEDVALUE('Table'[ExtensionAmt]) / 2) * 6,
            BLANK()
        ),
   SELECTEDVALUE('Table'[ProductType_Sub3]) = "monthly", SELECTEDVALUE('Table'[ExtensionAmt]) * 36,
    SELECTEDVALUE('Table'[ProductType_Sub3]) = "yearly", SELECTEDVALUE('Table'[ExtensionAmt]) * 3,
    BLANK()
)

Best regards,

Joyce

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

[Brightsider]

Try:

IF([InvoiceDate] > DATE(2020, 12, 31),
SWITCH( TRUE,
    [ProductType_Sub3] = "monthly", ([ExtensionAmt * 72),
    [ProductType_Sub3] = "yearly", ([ExtensionAmt * 6),
    [ProductType_Sub3] = "5 Year", (([ExtensionAmt / 5) * 6),
    [ProductType_Sub3] = "4 Year", (([ExtensionAmt / 4) * 6),
    [ProductType_Sub3] = "3 Year", (([ExtensionAmt / 3) * 6),
    [ProductType_Sub3] = "2 Year", (([ExtensionAmt / 2) * 6),
    FALSE),
SWITCH(TRUE,
    [ProductType_Sub3] = "monthly", ([ExtensionAmt * 36),
    [ProductType_Sub3] = "yearly", ([ExtensionAmt * 3),
    FALSE
))

I think captured what the formula's trying to do. You might have to monkey with it to get it to fit. 🙂 

[Greg_Deckler]

@CiaraCaryl Technically the same IF syntax should technically work in DAX. That said, a SWITCH( TRUE(), ... ) statement would be preferred. That's a lot of nested IF statements though. It's hard to decipher with the poor formatting so I would start by properly formatting the IF statements and then it should be easier to figure out the logic.