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!
v-kelly-msft's avatar
v-kelly-msft
Community Support
6 years ago

Hi Anonymous ,

 

Try :

if(isblank([Plan]);0;CALCULATE([Actual] - [Plan]))

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    v-kelly-msft mahoneypat amitchandak  Thank you reply. I don't want zeros after today. So 21 July and after should be blank. All of your solutions include zeros after today 🙂

    • mahoneypat's avatar
      mahoneypat
      Microsoft Employee
      6 years ago

      Please try this instead.

       

      NewMeasure =
VAR thisdate =
    MIN ( 'Date'[Date] )
VAR maxfactdate =
    CALCULATE ( MAX ( Fact[Date] ), ALL ( Fact ) )

RETURN
    IF (
        thisdate <= maxfactdate ,
        [Actual] - [Plan] + 0,
        [Actual] - [Plan]
    )

       

      If that doesn't work, please return just the thisdate and maxfactdate variables in the return to see where the logic is breaking down for troubleshooting.

       

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

      Regards,

      Pat

    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support
      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!