lookup value from table

[Allpat64]

Trying to write a Dax expression that calculates the variance between sales YTD minus sales prior year YTD

i have the measures written for sales YTD and sales prior year YTD.

i have no problem with an expression sales YTD - prior YTD.

my struggle is how do I incorporate a table value into the expression.

i want to treat revenue amounts different than expense amounts.

[v-juanli-msft · Accepted solution]

Hi @Allpat64

According to your last post, it seems you have found the solution there.

Use this formula

YTD_Variance_Matrix =
CALCULATE (
    TOTALYTD (
        [MTD_Actuals_Matrix],
        V_FS_DIM_DATE[act_date]
    ) - TOTALYTD (
            [MTD_Budget_Matrix],
            V_FS_DIM_DATE[act_date]
        ),
    KPI_Finance_Matrix[Group] = "Revenue"   //a filter condition
) + (
        CALCULATE (
            TOTALYTD (
                [MTD_Budget_Matrix],
                V_FS_DIM_DATE[act_date]
            ) - TOTALYTD (
                    [MTD_Actuals_Matrix],
                    V_FS_DIM_DATE[act_date]
                ),
            KPI_Finance_Matrix[Group] = "Operating Expenses"
 ) )

As this example shown, you could use a Boolean expression as a filter in the function CALCULATE to change the context in which the data is filtered, and evaluates the expression in the new context that you specify.

Reference:

CALCULATE(<expression>,<filter1>,<filter2>…)

Best Regards

Maggie

Best Regards

Maggie

[Stachu]

can you share the sample data in copiable format as well as the syntax for your measures?

you can find advice how to post these here

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490