Forum Discussion

harirao's avatar
harirao
Post Prodigy
3 years ago

DAX calculation

Hi All,

I have created an excel formula, which is basically comparing 4 columns using IF, AND & SUMIF functions
Excel formula:
=IF(AND(Q2>M2,SUMIFS($Q:$Q,$A:$A,$A2,$J:$J,$J2)>=SUMIFS($M:$M,$A:$A,$A2,$J:$J,$J2)),"Not enough fcst on the VT",IF(AND(Q2>M2,SUMIFS($Q:$Q,$A:$A,$A2,$J:$J,$J2)<=SUMIFS($M:$M,$A:$A,$A2,$J:$J,$J2)),"Enough fcst on the VT but not on the Loc","Enough fcst on the VT & Loc"))


Similar to excel formula, tried to create a new DAX but I am getting bellow error 
"Total Demand" is only Measure created others are direct columns from 'Main Table'
TEST =

IF(AND('Main Table'[Total Demand] > 'Main Table'[Final Fcst],
        CALCULATE(
            SUM('Main Table'[Total Demand]),
            FILTER(ALL('Main Table'[Product], 'Main Table'[Time[Month]]),
                'Main Table'[Product] = EARLIER('Main Table'[Product]) && 'Main Table'[Time[Month]] = EARLIER('Main Table'[Time[Month]])
            )) >= CALCULATE(
            SUM('Main Table'[Final Fcst]),
            FILTER(ALL('Main Table'[Product], 'Main Table'[Time[Month]]),
                'Main Table'[Product] = EARLIER('Main Table'[Product]) && 'Main Table'[Time[Month]] = EARLIER('Main Table'[Time[Month]])
            ))),
    "Not enough fcst on the VT",
    IF(AND('Main Table'[Total Demand] > 'Main Table'[Final Fcst],
            CALCULATE(SUM('Main Table'[Total Demand]),
                FILTER(ALL('Main Table'[Product], 'Main Table'[Time[Month]]),
                    'Main Table'[Product] = EARLIER('Main Table'[Product]) && 'Main Table'[Time[Month]] = EARLIER('Main Table'[Time[Month]])
                )) <= CALCULATE(SUM('Main Table'[Final Fcst]),
                FILTER(ALL('Main Table'[Product], 'Main Table'[Time[Month]]),
                    'Main Table'[Product] = EARLIER('Main Table'[Product]) && 'Main Table'[Time[Month]] = EARLIER('Main Table'[Time[Month]])
                ))),"Enough fcst on the VT but not on the Loc",
        "Enough fcst on the VT & Loc"))))))))))))
Error: The end of the input was reached.

Please assist me to resolve this issue
 
Thank you.
Need Help
Tips and Tricks
Tutorial Requests

1 Reply

  • marcelsmaglhaes's avatar
    marcelsmaglhaes
    Super User
    3 years ago

    Hey harirao 

     

    In your DAX has a lot of brackets and parentheses left over.  Take a look at the clean DAX below. I did not check the logic of the DAX,  I just check the brackets and parentheses. If not works, maybe you should try to use the SWITCH function (SWITCH function (DAX) - DAX | Microsoft Learn). It's easier to write than a cascade IF.

     

    Kind Regards, 
    Marcel

    ** If this posts helps, please consider mark it as solved. 

     

    TEST =
    IF (
    AND (
    'Main Table'[Total Demand] > 'Main Table'[Final Fcst],
    CALCULATE (
    SUM ( 'Main Table'[Total Demand] ),
    FILTER (
    ALL ( 'Main Table'[Product], 'Main Table'[Time[Month] ),
    'Main Table'[Product] = EARLIER ( 'Main Table'[Product] )
    && 'Main Table'[Time[Month] = EARLIER ( 'Main Table'[Time[Month] )
    )
    )
    >= CALCULATE (
    SUM ( 'Main Table'[Final Fcst] ),
    FILTER (
    ALL ( 'Main Table'[Product], 'Main Table'[Time[Month] ),
    'Main Table'[Product] = EARLIER ( 'Main Table'[Product] )
    && 'Main Table'[Time[Month]] = EARLIER('Main Table'[Time[Month]
    )
    )
    ),
    "Not enough fcst on the VT",
    IF (
    AND (
    'Main Table'[Total Demand] > 'Main Table'[Final Fcst],
    CALCULATE (
    SUM ( 'Main Table'[Total Demand] ),
    FILTER (
    ALL ( 'Main Table'[Product], 'Main Table'[Time[Month] ),
    'Main Table'[Product] = EARLIER ( 'Main Table'[Product] )
    && 'Main Table'[Time[Month] = EARLIER ( 'Main Table'[Time[Month] )
    )
    )
    <= CALCULATE (
    SUM ( 'Main Table'[Final Fcst] ),
    FILTER (
    ALL ( 'Main Table'[Product], 'Main Table'[Time[Month] ),
    'Main Table'[Product] = EARLIER ( 'Main Table'[Product] )
    && 'Main Table'[Time[Month] = EARLIER ( 'Main Table'[Time[Month] )
    )
    )
    ),
    "Enough fcst on the VT but not on the Loc",
    "Enough fcst on the VT & Loc"
    )
    )