New Items minus Superseded Items

DAX DAX Calculations
1

Hello,

I have the following question: I would like to calculate the revenue for the corresponding item, minus the revenue of the item to be superseded. In the first step it would be.

image

I have the following model available and I can’t change much in it, because two different datasets (New Items & Supersession come from one dataset, the Invoiced Transaction data + Date-Table come from another dataset) are combined, so it’s a hybrid model.

image
image928×462 17.6 KB

Unfortunately I can’t provide the original data, but only a test model.

I was able to transfer the Item to be Superseded with TREATAS, but the sum is not correct at the end, because it does not filter the table properly. However, I don’t know what to change.

Item to be Superseded =
CALCULATE(
[Sum Invoiced Amount],
TREATAS(
VALUES(‘Supersession’[Item to be Superseded]), ‘New Items’[Item])
)

In the second step, I would like to use the Release Date from the New Items Table to calculate the sales for the last 36 months, if there are sales, to calculate the average monthly sales from that, and then mutiply it with the number of months of sales from the Release Date. So the goal is to get a realistic New Items Revenue. However, there is no link between the ‘Date Table’ [Date] and the New Items [Released Date]. How can I solve this?

I know I’m asking for magic here with the limited relationships and the hyprid model, at least that’s what I feel, and I hope someone can help me. Thank you very much!

Supersession.pbix (73.9 KB)

2

If I understood your question properly, I believe this is the measure you need:

eDNA Solution = 
    SUMX(
        ADDCOLUMNS(
            VALUES( 'New Items'[Item] ), 
                "@S_Amt", [Item to be Superseded],        
                "@Amt", [Sum Invoiced Amount] ),
    IF( ISBLANK( [@S_Amt] ), 0, [@Amt] - [@S_Amt] ))

and here it is in the visual:
image

3

Hello @Steven1

Did the response from @Heather (thank you!) help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION.

Thank you

4

Hi,

thank you for your help. Yes, that worked. Thanks a lot.

Kind regards,

Steven1

1 Like