Forum Discussion

spandy34's avatar
spandy34
Icon for Responsive Resident rankResponsive Resident
3 years ago
Solved

Measure - Subtract one field from another

I have created a column with the following 

 

Diff in Per No Previous = if(isblank([Previous Performance]),0,[Current Performance]-[Previous Performance])

 

I would like to create a measure creating the same so I can make arrows for conditional formatting.  Can someplease please tell me how I can write the DAX for the above code.  The table where Current Performance and Previous Performance fields are is called Performance 

 

Many thanks

 

 

Anonymous , tamerj1 amitchandak 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi spandy34 ,

     

    If [Previous Performance] and [Current Performance] are measures, you can use them directly.

    If [Previous Performance] and [Current Performance] are columns, you need to use SUM()/MAX()/MIN() function to use them. Measure will return aggregation.

    Measure:

    Diff in Per No Previous =
IF (
    ISBLANK ( SUM ( 'TableName'[Previous Performance] ) ),
    0,
    SUM ( 'TableName'[Current Performance] )
        - SUM ( 'TableName'[Previous Performance] )
)

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi spandy34 ,

     

    If [Previous Performance] and [Current Performance] are measures, you can use them directly.

    If [Previous Performance] and [Current Performance] are columns, you need to use SUM()/MAX()/MIN() function to use them. Measure will return aggregation.

    Measure:

    Diff in Per No Previous =
IF (
    ISBLANK ( SUM ( 'TableName'[Previous Performance] ) ),
    0,
    SUM ( 'TableName'[Current Performance] )
        - SUM ( 'TableName'[Previous Performance] )
)

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Adamboer's avatar
    Adamboer
    Icon for Responsive Resident rankResponsive Resident
    3 years ago

     

    Sure! You can create a measure that calculates the difference between the current and previous performance values by using the DAX formula:
    Diff in Per No Previous Measure = IF(ISBLANK(MAX(Performance[Previous Performance])), 0, MAX(Performance[Current Performance]) - MAX(Performance[Previous Performance]))

    This measure uses the MAX function to get the maximum value of the previous and current performance columns from the Performance table. It then checks if the previous performance value is blank, and if so, returns 0. Otherwise, it calculates the difference between the current and previous performance values. You can use this measure to create arrows for conditional formatting based on the difference between the current and previous performance values.