Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
spandy34
Responsive Resident
Responsive Resident

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

 

 

@v-shex-msft , @tamerj1 @amitchandak 

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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.

View solution in original post

2 REPLIES 2
Adamboer
Responsive Resident
Responsive Resident

 

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.


v-rzhou-msft
Community Support
Community Support

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.