DAX calculation

[harirao]

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.

[marcelsmaglhaes]

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"
)
)