cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hnowotny17
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

 

hnowotny17_0-1612538880454.png

 

 

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

 

1 ACCEPTED SOLUTION
amitchandak
Super User IV
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!

View solution in original post

7 REPLIES 7
v-yangliu-msft
Community Support
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.

v-yangliu-msft
Community Support
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.

v-yangliu-msft_0-1612831032103.png

 

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.

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
Solution Specialist
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 )
    )

 1.PNG

Hope that helps!

 

Best regards,

Tim

hnowotny17
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

amitchandak
Super User IV
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!

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors