Complex problem with several criteria using PBI / DAX

[capko]

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):

Item	Code	Date 1	Date 2	Currency	Value	Value USD	Source
1		01/07/2020	01/12/2024	USD	18.85	18.85	AB
1		21/06/2022	21/12/2027	EUR	9.42	10.17	AB
1		21/06/2022	21/12/2027	USD	19.00	19.00	AB
1	A	14/04/2021	14/05/2023	USD	6.28	6.28	AC
1		02/04/2020	02/12/2023	USD	6.28	6.28	AD
1		01/01/2025	31/12/2027	USD	7.50	7.50	AD
1	A	14/04/2021	14/05/2023	USD	6.70	6.70	AE
1		02/04/2020	02/12/2023	USD	5.20	5.20	AE

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:

Item	Value (Source AB)	Valid (Source AB)	Value (Source AC)	Valid (Source AC)	Value (Source AD)	Valid (Source AD)	Value (Source AE)	Valid (Source AC)	Final Value	Dispersion
1	14.59	Yes	N/A	Code A	7.5	Yes	5.2	Out of date	9.42	Warning

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 🙂

[lucadelicio · Accepted solution]

Try

Measure =

var _a =

CALCULATE(

    AVERAGE(Foglio1[    Value USD    ])

    ,Foglio1[Valid Date] = 1

    ,Foglio1[    Code    ] <> "A"

)

var _b =

CALCULATE(

    AVERAGE(Foglio1[    Value USD    ])

    ,Foglio1[    Code    ] <> "A"

)

var _c =

CALCULATE(

    AVERAGE(Foglio1[    Value USD    ])

)

RETURN

IF(ISBLANK(_a) && ISBLANK(_b), _c

    ,IF(ISBLANK(_a), _b

        ,_a

    )

)


Second measure for the text:

Measure 2 =

var _a =

CALCULATE(

    AVERAGE(Foglio1[    Value USD    ])

    ,Foglio1[Valid Date] = 1

    ,Foglio1[    Code    ] <> "A"

)

var _b =

CALCULATE(

    AVERAGE(Foglio1[    Value USD    ])

    ,Foglio1[    Code    ] <> "A"

)

var _c =

CALCULATE(

    AVERAGE(Foglio1[    Value USD    ])

)

RETURN

IF(ISBLANK(_a) && ISBLANK(_b), "Code A"

    ,IF(ISBLANK(_a), "Out of dates"

        , "Yes"

    )

)

And put both into a matrix:
Row item
Columns: Source

[lucadelicio]

As attachment the pbix file

[capko]

It's not compatible with my version of PBI and I cannot install the last one 😞

[lucadelicio]

Hi @capko 
can you show tha calculation for the Value (Source AB)?
If i undestrand well it will be the avg of 18,85, 10,17 and 19,00. The valid line for item 1 source AB and the result will be 16,01.
I forgot something?
Why you are show 14,59?
Thank you

[capko]

Hi ! You're completely right, it has been an error on my side 🙂