cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculation Difference Between Two Numbers in Two Tables Only

I am trying to compare the price of two different products across time, but on some dates I might not have price date for both products, so I want to exclude dates.  As you can see on the bottom of the picture, I didn't have both prices on September 3, so when I calculate to subtract those two numbers, it assumes that's a zero instead of omitting it.

Here is my current calculation:

GAP = 'Daily Price 1'[\$]-related('Daily Price 2'[\$])

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Member

## Re: Calculation Difference Between Two Numbers in Two Tables Only

Hi,

with a if statement you can check if both prices exists.

GAP = IF( ISBLANK('Daily Price 1'[\$]) || ISBLANK(RELATED('Daily Price 2'[\$])), BLANK(), 'Daily Price 1'[\$]-RELATED('Daily Price 2'[\$]) )

The BLANK() GAP can be filtered on the visual level

2 REPLIES 2
Super User

## Re: Calculation Difference Between Two Numbers in Two Tables Only

There is a visual level filter you can set to filter out zeros etc.

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
Member

## Re: Calculation Difference Between Two Numbers in Two Tables Only

Hi,

with a if statement you can check if both prices exists.

GAP = IF( ISBLANK('Daily Price 1'[\$]) || ISBLANK(RELATED('Daily Price 2'[\$])), BLANK(), 'Daily Price 1'[\$]-RELATED('Daily Price 2'[\$]) )

The BLANK() GAP can be filtered on the visual level

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 301 members 3,110 guests
Recent signins: