How can I calculate the diff between two products from different tables and with a set of conditions

[Anonymous]

I've got two different types of diesel, each of them with different prices.

I've also got calculated Moving Averages of both, and I need to see the average GAP between them but under the condition they need to have a value in the same DAY to calculate such average, otherwise it wouldn't be valid. The tables and expected result is kind of as follows:

TABLE DIESEL TYPE A

Date	Price DIESEL TYPE A
01-feb	1,2
05-may	1,3
06-ago	1,09
06-ago	1,1
07-sep	1,5

TABLE DIESEL TYPE B

Date	Price DIESEL TYPE B
01-feb	0,9
05-may	1,05
06-ago	0,8
06-ago	0,75
12-nov	0,7

Date Average A Average B

01-feb	1,2	0,9
05-may	1,3	1,05
06-ago	1,095	0,775
07-sep	1,5	-
12-nov	-	0,7

The expected GAP should be:

Date GAP Average

01-feb	0,30
05-may	0,25
06-ago	0,32
07-sep	-
12-nov	-

In September 7th and November 12th I DONT want to have these averages calculated or shown on my graph, i.e. on my measure.

In summary, i wanna get an average of the difference between these two prices by date and under the condition there should be values for the same date in both type of diesels, otherwise I don't want to calcu

[FreemanZ · Accepted solution]

try this?

GapAvg = 

IF(

    [Average A]<>BLANK() && [Average B]<>BLANK(),

    [Average A] - [Average B]

)

[TonyZhou1980 · Accepted solution]

My solution is to create a measure as below, and create a date table to connect both table type A and type B.  Then you can configure the visual accordingly.

Diff =

var _avgA=average(TYPEA[Price DIESEL TYPE A])

var _avgB=average(TYPEB[Price DIESEL TYPE B])

return

    if(

        or(_avgA=BLANK(),_avgB=BLANK()),

        blank(),

        _avgA-_avgB

    )

[FreemanZ · Accepted solution]

try this?

GapAvg = 

IF(

    [Average A]<>BLANK() && [Average B]<>BLANK(),

    [Average A] - [Average B]

)

[Anonymous]

EXACTLY THIS!!! 

I've looking for this the whooooole day. I appreciate so much your help.

[FreemanZ]

thank you as well. Not every suggestion is varified with real data by the advisor. So your timely feedback is extremely important.