cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

lookup value from table

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

Re: lookup value from table

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

2 REPLIES 2
Super User

Re: lookup value from table

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

Community Support Team

Re: lookup value from table

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