cancel
Showing results for
Did you mean:
Frequent Visitor

Sold Value counted from two Pricelist

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

1 ACCEPTED SOLUTION
Super User IV

@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()
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

7 REPLIES 7
Community Support

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.

Community Support

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.

Frequent Visitor

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

Solution Specialist

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

Frequent Visitor

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

Super User IV

@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()
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Frequent Visitor

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

Announcements

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

Check out how to claim yours today!

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors