Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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()
)
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.
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.
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
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
Proud to be a Super User!
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
@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()
)
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |