Variable not evaluating inside switch statement, individual calculation is working fine.

[Anonymous]

Hello When i combine Variables with Switch statement it doesn’t work. But instead of variables I write entire calculation it work perfectly.
Below are 2 scenario of my measure, The first scenario doesn’t work if i use variable, but second work perfectly if i don't use variables. (The Dark Grey Bar's are the one causing issue)
Can anyone please help what i am doing wrong in first scenario.
merry christmas

scenario 1 (Incorrect) :-

Total_workforce_FLC EURO(M/Y-1) =

Var FirstSUm = CALCULATE(SUM(MOR_Data[Current month Fully Loaded Costs Euro]),MOR_Data[STATUS] <> "Contingent",SAMEPERIODLASTYEAR('Calendar'[Date]))

Var SecondSum = CALCULATE(SUM(MOR_Data[Current month Fully Loaded Costs Euro]),MOR_Data[STATUS] = "Contingent",SAMEPERIODLASTYEAR('Calendar'[Date]))

return

if(HASONEVALUE('Axis_Table'[Type]),

SWITCH(VALUES(Axis_Table[Type])

,"Year",CALCULATE(FirstSUm +

SecondSum

,TREATAS(VALUES('Axis_Table'[Values])

,'Calendar'[Year_Cal]))

,"Month",CALCULATE(FirstSUm +

SecondSum

,TREATAs(VALUES('Axis_Table'[Values])

,'Calendar'[MonthYear_Cal]))

,"Quarter",CALCULATE(FirstSUm +

SecondSum

,TREATAs(VALUES('Axis_Table'[Values])

,'Calendar'[Quater-Year]))

)

)

scenario 2(Correct) :-

Total_workforce_FLC EURO(M/Y-1) =

if(HASONEVALUE('Axis_Table'[Type]),

SWITCH(VALUES(Axis_Table[Type])

,"Year",CALCULATE(CALCULATE(SUM(MOR_Data[Current month Fully Loaded Costs Euro]),MOR_Data[STATUS] <> "Contingent",SAMEPERIODLASTYEAR('Calendar'[Date])) +

CALCULATE(SUM(MOR_Data[Current month Fully Loaded Costs Euro]),MOR_Data[STATUS] = "Contingent",SAMEPERIODLASTYEAR('Calendar'[Date]))

,TREATAS(VALUES('Axis_Table'[Values])

,'Calendar'[Year_Cal]))

,"Month",CALCULATE(CALCULATE(SUM(MOR_Data[Current month Fully Loaded Costs Euro]),MOR_Data[STATUS] <> "Contingent",SAMEPERIODLASTYEAR('Calendar'[Date])) +

CALCULATE(SUM(MOR_Data[Current month Fully Loaded Costs Euro]),MOR_Data[STATUS] = "Contingent",SAMEPERIODLASTYEAR('Calendar'[Date]))

,TREATAs(VALUES('Axis_Table'[Values])

,'Calendar'[MonthYear_Cal]))

,"Quarter",CALCULATE(CALCULATE(SUM(MOR_Data[Current month Fully Loaded Costs Euro]),MOR_Data[STATUS] <> "Contingent",SAMEPERIODLASTYEAR('Calendar'[Date])) +

CALCULATE(SUM(MOR_Data[Current month Fully Loaded Costs Euro]),MOR_Data[STATUS] = "Contingent",SAMEPERIODLASTYEAR('Calendar'[Date]))

,TREATAs(VALUES('Axis_Table'[Values])

,'Calendar'[Quater-Year]))

)

)

[mwegener · Accepted solution]

Hi @Anonymous ,

check this out.

Test Measure2 = 
CALCULATE (
    CALCULATE (
        SUM ( MOR_Data[Current month Fully Loaded Costs Euro] ),
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
    ),
    FILTER (
        'Calendar',
        CONTAINS (
            VALUES ( Axis_Table[Values] ),
            Axis_Table[Values], SWITCH (
                SELECTEDVALUE ( Axis_Table[Type]),
                "Year", 'Calendar'[Year_Cal],
                "Quarter", 'Calendar'[Quater-Year],
                "Month", 'Calendar'[MonthYear_Cal]
            )
        )
    )
)

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.