Sold Value counted from two Pricelist

[hnowotny17]

Hello,

I would like to kindly ask anyone for the help.

I have a table with products, sold quantity and pricelist (price/product) and I would like to count "Sold Value" (Sold quantity*Price). The only problem is, that I would like to apply 2 different pricelist. For products sold between JAN 2020 - APR 2020 to use "Pricelist S1" and for products sold between MAY 2020 - JAN 2021 apply "Pricelist S2".

Does anyone know the correct formula for this case?

Many thanks and have a nice day,

Jan Novotny

PBI file: https://drive.google.com/file/d/18xVmzPC_c-6VBBhHFXxR7GMvy4grmque/view?usp=sharing

[v-yangliu-msft]

Hi  @hnowotny17 ,

would you like to mark my reply as a solution so that others can learn from it too?

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yangliu-msft]

Hi @hnowotny17  ,

Here are the steps you can follow：

1. Create measure.

Sold Value =
IF(MAX('Sell out Mallzart'[Date])>=DATE(2020,1,1)&&MAX('Sell out Mallzart'[Date])<=DATE(2020,4,1),

MAX('Sell out Mallzart'[Sold Quantity])*CALCULATE(SUM('Pricelist S1'[Price]),FILTER('Pricelist S1','Pricelist S1'[Ref.no]=MAX('Sell out Mallzart'[Ref.no]))),

MAX('Sell out Mallzart'[Sold Quantity])*CALCULATE(SUM('Pricelist S2'[Price]),FILTER('Pricelist S2','Pricelist S2'[Ref.no]=MAX('Sell out Mallzart'[Ref.no]))))

2. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hnowotny17]

Dear @v-yangliu-msft 

Many thanks for your time! I follow your steps and it worked as well!

Kind regards and thank you again,

JN

[timg]

Hi hnowotny17,

Here's an example based on test data. In the image you'll see that this seperates the calculation betweeen pre and post april 2020 data. (The sales amount * 1 and *1000 are just to show the difference, replace this with your quantity*price1 and quantity*price2 logic)

Calculate2Prices =
CALCULATE (
    SUM ( FactSales[Sales Amount] ) * 1,
    FILTER ( DimDate, DimDate[MonthKey] >= 202001 && DimDate[MonthKey] <= 202004 )
)
    + CALCULATE (
        SUM ( FactSales[Sales Amount] ) * 10000,
        FILTER ( DimDate, DimDate[MonthKey] >= 202005 && DimDate[MonthKey] <= 202101 )
    )

 

Hope that helps!

Best regards,

Tim

[hnowotny17]

Dear @timg,

Thank you for your time! I follow your steps, it took me little bit longer to understand (as I am not profi user of PBI), but in the end it worked!

Thank you again and have a nice day,

Jan N

[amitchandak · Accepted solution]

@hnowotny17 , Try a new column like

Switch( True(),
[Date] >= date(2020,01,01) && [Date] <= date(2020,04,30) , [Pricelist S1] * [sold quantity],
[Date] >= date(2020,05,01) && [Date] <= date(2021,01,31) , [Pricelist S2] * [sold quantity],
blank()
)

[hnowotny17]

Dear @AmitK 

thank you for your time to help me with this case. I tried your solution and it perfectly works!

Kind regards,

Jan N