cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Allpat64 Frequent Visitor
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
Community Support Team

Re: lookup value from table

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

 

 

 

2 REPLIES 2
Super User
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

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

Community Support Team
Community Support Team

Re: lookup value from table

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