Measure not give the same result inside SWITCH

sam.mckay · June 17, 2019, 10:55pm

Have had a quick look through here…all I can put it down to is the VALUES part of the formula.

I tested a few things to see why and you’ll see here that if you only have this column against a column from the date table then it ends at June 2019, so it is only evaluating to true up to that point.

You’ll need some other piece of logic there. Something like the below.

Total Overhead LY Test 3 = 
VAR AcctCategory = CALCULATE( SELECTEDVALUE( CashFlowTransactions[Account Category 1], BLANK() ), ALL( 'Calendar' ) )

RETURN
    SWITCH( 
        TRUE(),
        AcctCategory = "Freight Cost", [Expense Freight Cost LY],
        AcctCategory = "IT Related Expenses", [Expense IT Related Expenses LY],
        AcctCategory = "Marketing Expenses", [Expense Marketing Expenses LY],
        AcctCategory = "Other Costs", [Expense Other Costs LY],
        AcctCategory = "Rent", [Expense Rent LY],
        AcctCategory = "Salaries", [Expense Salaries LY],
        AcctCategory = "Staff Development Cost", [Expense Staff Development Cost LY],
        AcctCategory = "Tax Expense", [Expense Tax Expense LY],
        AcctCategory = "Utilities Cost", [Expense Utilities Cost LY],
    BLANK()
    )

You’ll see I need to use the ALL( Calendar ) inside CALCULATE, because the Account Category column was always ending at June and it needed to be down across all dates for the formula to work as you needed.

I tested a few things before I landed on the right answer.

Sam