Forum Discussion

T-Joyce's avatar
T-Joyce
Frequent Visitor
6 years ago

Sales Data and Price Lists

Hi, I have two main tables of data (Sales and Net Price) plus a date table which includes season year (eg. Spring 19), product table and customer table. The sales table lists all sales transactions ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

HI T-Joyce ,

You can try to use the following measure formula if it meets for your requirement:

Measure =
VAR currCode =
    SELECTEDVALUE ( Sales[Item Code] )
VAR currDate =
    MAX ( Sales[Transaction Date] )
VAR seasonYear =
    CALCULATE (
        MAX ( 'Calendar'[SeasonYear] ),
        FILTER ( ALLSELECTED ( 'Calendar' ), [Date] = currDate )
    )
VAR _price =
    CALCULATE (
        MAX ( Price[Net Price (Kg/Lt)] ),
        FILTER (
            ALLSELECTED ( Price ),
            Price[Item Code] = currCode
                && Price[SeasonYear] = seasonYear
        )
    )
RETURN
    CALCULATE (
        MAX ( Sales[Quantity (Kg/Lt)] ),
        VALUES ( Sales[Item Code] ),
        VALUES ( Sales[Transaction Date] )
    ) * _price

Regards,
Xiaoxin Sheng

T-Joyce's avatar
T-Joyce
Frequent Visitor
6 years ago

Hi, thank you for your response, this has almost solved it however, the 'customer code' needs to be in here somewhere as we have different net prices for each customer in each season.