Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Matrix: Variance blank fields

Hi Everyone, I created the below matrix but I need help to create an optimal variance between Plan and Actual.  Right now I made the following DAX formula to measure Var: Var = IF([Actual] = ...
Need Help
Show and Tell
Tips and Tricks
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi  Anonymous ,

     

    Modify your measure as below:

    If(MAX[date]>=TODAY(),BLANK(),if(isblank([Plan]);0;CALCULATE([Actual] - [Plan])))
     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

Assuming your matrix is using columns from your Date/Calendar table, you might approach it like this:

 

NewMeasure =
VAR thisdate =
    MIN ( 'Date'[Date] )
VAR maxfactdate =
    CALCULATE ( MAX ( Fact[Date] ), ALL ( Fact ) )
VAR thisactual = [Actual]
RETURN
    IF (
        AND ( ISBLANK ( thisactual ), thisdate <= maxfactdate ),
        0,
        thisactual - [Plan]
    )

 

It finds the max date for which you have data (maxfactdate) and uses that in the logic.  It also calculates your Actual as a variable, so it is calculated only once for performance reasons.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat