Forum Discussion

jessicarocha's avatar
jessicarocha
Icon for Helper IV rankHelper IV
2 years ago

SWITCH not giving total

Hello, 

 

I need a formula that allows multiple conditions. Like if, else if, else statement. I read about and SWITCH seemed like a solution. 

 

So, my formula is:

 

 

IST + Plan= 
SWITCH(TRUE(),
    MAX(Date[BusinessYear]) == [CurrentBusinessYear] && SUM(History[ActualQty]) = BLANK(), SUM(Plan[PlanQty]),
    MAX(Date[BusinessYear]) ==[CurrentBusinessYear] && SUM(History[ActualQty]) <> BLANK(), SUM(History[ActualQty]),
    MAX(Date[BusinessYear]) <>[CurrentBusinessYear],
    BLANK()
    )

 

 

 

The formula gives the correct result per row. However, no total is presented: 

 

My wish was to obtain a sum for the current business year. The measure should contain a mix of actual and planned values. When there is a actual value already (for past months), then sum the actuals. Otherwise, for the future months, sum the plan values.

 

PS: I tried to reproduce this example in a dummy file but I was not able to replicate the problem. In my dummy example the function seems to work.

Do you have any idea what could be the problem?

Or how to use a diferent formula that does not involve SWITCH? 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi jessicarocha ,
    Please try below dax formula:

     

    IST + Plan =
    VAR _a =
        SWITCH (
            TRUE (),
            MAX ( Date[BusinessYear] )
                == [CurrentBusinessYear]
                    && SUM ( History[ActualQty] ) = BLANK (), SUM ( Plan[PlanQty] ),
            MAX ( Date[BusinessYear] )
                == [CurrentBusinessYear]
                    && SUM ( History[ActualQty] ) <> BLANK (), SUM ( History[ActualQty] ),
            MAX ( Date[BusinessYear] ) <> [CurrentBusinessYear], BLANK ()
        )
    VAR _b =
        SUMX ( Date, _a )
    RETURN
        IF ( HASONEVALUE ( Date[BusinessYear] ), _a, _b )
    

     

     

     Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.