Calculating Variance with Analysis Services as the source

[GarethWoodhouse]

Hi all.

I am trying to caculate the variance period on period per customer.

My Period value is in table date 

My Customer value is in table customer

My Value is in table Sales

Customer          Period          Value

A                       1                   100

A                       2                   200

A                       3                   100

B                       1                    300

B                       2                    250

B                       3                    200

This Measure works as expected

Value1 =

VAR periodvalue = value(SELECTEDVALUE('Date LookUp'[Financial Period]))

Return Calculate('Sales Invoice History'[Gross Profit Post BDM], FILTER('Date LookUp','Date LookUp'[Financial Period] = periodvalue))

However This Measure returns blank

Value2 =

VAR periodvalue = value(SELECTEDVALUE('Date LookUp'[Financial Period]))

Return calculate(sum('Sales Invoice History'[STG_Gross_Profit_Less_BDM]), FILTER('Date LookUp','Date LookUp'[Financial Period] = periodvalue +1))

My plan was to do Value1 - Value2 to get the variance and drop the measure into my table to calculate by Customer

If anyone can help or shed some lighton where I am going wrong it would be appreciated.

Thanks.

[v-lili6-msft · Accepted solution]

hi @GarethWoodhouse 

Just add "ALL" in the [Value2] measure as below:

Value2 =
VAR periodvalue =
    VALUE ( SELECTEDVALUE ( 'Date LookUp'[Financial Period] ) )
RETURN
    CALCULATE (
        SUM ( 'Sales Invoice History'[STG_Gross_Profit_Less_BDM] ),
        FILTER (
            ALL ( 'Date LookUp' ),
            'Date LookUp'[Financial Period] = periodvalue + 1
        )
    )

Then it will work well.

Regards,

Lin

[GarethWoodhouse]

Initially this didn't work for me but I then realised it was because I also had a Year field and the All on the filter was bypassing this page filter.

By adding a filter on selected year into the value2 code I got this working perfectly.

Thanks all for your help.

For anyone else experiencing similar problems here is the final code that works for my exmple

GP Variance =

VAR periodvalue1 =

    VALUE ( SELECTEDVALUE ( 'Date LookUp'[Financial Period] ) )

VAR Value1 =

    CALCULATE (

sum('Sales Invoice History'[STG_Gross_Profit_Less_BDM]),

        FILTER (

            'Date LookUp',

            'Date LookUp'[Financial Period] = periodvalue1

        )

    )

VAR periodvalue2 =

    VALUE ( SELECTEDVALUE ( 'Date LookUp'[Financial Period] ) -1 )

VAR yearvalue2 =

    VALUE ( SELECTEDVALUE ( 'Date LookUp'[Financial Year] ) )

Var Value2 =

    CALCULATE (

        sum('Sales Invoice History'[STG_Gross_Profit_Less_BDM]),

        FILTER (

            all('Date LookUp'),

            'Date LookUp'[Financial Period] = periodvalue2 && 'Date LookUp'[Financial Year] = yearvalue2

        )

    )

Return IF(Value2 <> 0,Value1 - Value2,0)