Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Column (or measure) based on slicer value

Hello community.

I need assistance.

I have two tables: Sales and Prices.

The task is to calculate sales amount : amount = Sales.quantity *  Prices.latest Price for that SKU, which depends on price type, selected in slicer.

I did first part (found latest price by sku) by creating such column:

Spoiler
Amount =
VAR CurrentSKU = Sales[SKU]
VAR CurrentDate = Sales[Date]
VAR MaxDateOfPrices =
CALCULATE (
MAX ( Prices[PriceDate] );
Prices[PriceDate]<= CurrentDate;
Prices[SKU] = CurrentSKU
)
RETURN
Sales[Qty]*LOOKUPVALUE (
Prices[Price];
Prices[SKU]; CurrentSKU;
Prices[PriceDate]; MaxDateOfPrices
)

And that is ok. But I got stuck with the second part of the task (can't consider selected price type).

I've created measure:

SelectedPrice = SELECTEDVALUE(Prices[PriceType])
But my logic doesn't work, when I create new column, based on this measure:
Spoiler
AmountWithPrice =
VAR CurrentSKU = Sales[SKU]
VAR CurrentDate = Sales[Date]
VAR MaxDateOfPrices =
CALCULATE (
MAX ( Prices[PriceDate] );
Prices[PriceDate]<= CurrentDate;
Prices[SKU] = CurrentSKU
)
RETURN
LOOKUPVALUE (
Prices[Price];
Prices[SKU]; CurrentSKU;
Prices[PriceDate]; MaxDateOfPrices ;
Prices[PriceType];[SelectedPrice]
)
This column is empty.
Please advise.
Here is the file.

Thank you.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Anonymous Thanks for the explaining. so date in price table should be less then sales date.

    Measure = 
VAR _pricetype = SELECTEDVALUE(Prices[PriceType])
VAR _date = MAX(Sales[Date])
VAR _sku = MAX(Sales[SKU])
VAR _latestDate = CALCULATE(MAX(Prices[PriceDate]),Prices[SKU]=_sku,Prices[PriceType]=_pricetype,Prices[PriceDate]<=_date)
VAR _latestPrice = CALCULATE(MAX(Prices[Price]),Prices[SKU]=_sku,Prices[PriceDate]=_latestDate,Prices[PriceType]=_pricetype)
RETURN SUMX(Sales,Sales[Qty]*_latestPrice)

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous Please try below

    Measure = 
VAR _pricetype = SELECTEDVALUE(Prices[PriceType])
VAR _sku = MAX(Sales[SKU])
VAR _latestDate = CALCULATE(MAX(Prices[PriceDate]),Prices[SKU]=_sku,Prices[PriceType]=_pricetype)
VAR _latestPrice = CALCULATE(MAX(Prices[Price]),Prices[SKU]=_sku,Prices[PriceDate]=_latestDate,Prices[PriceType]=_pricetype)
RETURN SUMX(Sales,Sales[Qty]*_latestPrice)

    measure

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous thank you for reply.
      It is ok with price type, but now it is wrong with first part of the task.
      This measure takes latest price of all prices of that type.
      Take a look at example below, amount for sale 15.10.2019 is incorrect:

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Anonymous 
        Sorry I didn't get you what's wrong here
        for 15.10.2019 Quantity is 15 and sku is 11 so as per logic for sku 11 and price type A latest price is 333 hence total is 

        15*333 = 4995

        Please help me with expected result that you are looking for

         

        Do you mean price date should be less then equal to date?