SWITCH not giving total

[jessicarocha]

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? 

[Anonymous]

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.

[jessicarocha]

Hi @Anonymous ,

thank you for answering and trying to help me out. 

Unfortunatelly the formula did not work for me. I obtained the same result as with my formula. The row values are correct but there is no total presented.

However, I did find a solution that worked in a youtube video. Here is the link, in case someone else has the same problem. 

What's the missing LINK?! Combining ACTUALS and FORECAST as one LINE in Power BI - YouTube

Kind regards, 

Jéssica