Complex problem with several criteria using PBI / DAX

Hello,

I've an interesting problem to solve but I need some in order to implement it in PBI with DAX.

I've the following table with several items. These items will always have a price, a currency, a validity date, a suppression code (if this price has been suppressed from the system for any reason) and a source. The table will be something like this (the given example only contains one item but in the real one I can have thousands):

The idea is to get a final price for each item and for each one of the sources following several rules (with a priority order):

1. The first criterion will be the date: the final price must be valid today (Date 1 < Today < Date 2)
2. If there are not valid date lines, a not valid date will be accepted (e.g. no lines available respecting the previous criterion)
3. Lines with 'A' in column 'Code' will not be taken into account unless there is no other option (e.g. all lines for a given item and source are tagged 'A').
4. The final price will be the average between the lines respecting the criteria using the 'Value USD' column

The result table will be something like this:

For each one of the sources, it will get back the average value for the valid lines respecting the previous criteria. The final price will always be selecting respecting the priority order if the source has a valid price (Source AB > Source AC > Source AD).

The column 'Valid (Source XX)' will be -'Yes', 'Code A', 'Out of date'- depending of the case used to compute the price. The last column will indicate that the prices used to calculate the average has a dispersion > 20%.

I'll try to upload my firsts ideas on how to solve it today but I would like to have your suggestions since I know some experts are around here 🙂