Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Help Converting Excel Formula to DAX

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), ...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous , 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's avatar
Brightsider
Icon for Resolver I rankResolver I
2 years ago

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. 🙂