How to calculate average profit ONLY when the product has been bought and sold in selected time.

[dblw]

Hello. 

I need to calculate the average profit of per product and in general in the selected time period. It is for a scrap business that buys scrap and then sells it forward. However, I only want to calculate profits for a product that has been bought AND sold in the selected period, because otherwise it will calculate a loss if a product has been recently bought in the selected time period but not sold. Now I have created a DAX formula that can calculate this on single products. Formula looks like this: 

Kate keskimäärin =

IF(

    NOT(ISBLANK([Ostot keskimäärin €/T])) && NOT(ISBLANK([Myynti keskimäärin €/T])),

    [Myynti keskimäärin €/T] - Ostot[Ostot keskimäärin €/T],

    BLANK()

)
Where "Ostot keskimäärin €/T" is average buying price and "Myynti keskimäärin €/T" is average sales price. It works and calculates this on single products but when it comes to the overall average on all the products it calculates also the ones that how been bought but not sold and vice versa. 
 
As you can see in this table the total is calculated falsley since it includes everything. Does anybody know how I can get the average profit in total? It doesnt necessarily have to be visible in the table but I need the calculation to show the average profit All help will be appriciated. 

[FreemanZ · Accepted solution]

hi @dblw ,

try like:

Profit =

AVERAGEX(

    FILTER(

        tablename,

        NOT ISBLANK([buyingprice])

            &&NOT ISBLANK([sellingprice])

    ),

    [sellingprice] - [buyingprice]

)

[dblw]

This worked. Thank you!