Calculating sales since price expiration date

[dusdau]

Hi All,

I did some searching and couldn't come up with what I needed to solve my problem.  Basically I am trying to calculate Sales since the price on the part expired.  Considering these 3 tables and relationships.

Date Table (one to many with Sales Table)

Columns: Date, etc.

Part Table (one to many with Sales Table)

Part, Price Expiration Date, Days since expiration, etc.

Sales Table (Transactions)

Transaction Date, Amount, etc.

I want to CALCULATE the Sales[Amount] for the Part since the Price Expiration Date on the part.

Seems like I should be able to do this with DAX but I can't quite figure out how to filter the sales by the variable/dynamic price expiration date.

Any ideas? Thanks!

[VijayP · Accepted solution]

@dusdau 

Use this file for Solution and let me know if you are good with it! Will explain if you are not clear about the measures! Let me know!

[dusdau]

Thanks @VijayP and @jdbuchanan71 !  Exactly what I needed.  It does indeed work both ways.  Thanks so much!

[VijayP · Accepted solution]

@dusdau 

Use this file for Solution and let me know if you are good with it! Will explain if you are not clear about the measures! Let me know!

[jdbuchanan71 · Accepted solution]

Nice @VijayP !  I think you can do it with out adding the [Price Expired Date] to the sales table as well.

Sales of Expired Items = 
CALCULATE (
    [Total Sales],
    FILTER (
        Sales,
        RELATED ( Dates[Date] ) > RELATED ( Parts[Price Expired Date] )
    )
)

[VijayP]

@jdbuchanan71 Yes you are right! I created this poc for @dusdau quickly and later realised I can use that as well!