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.
Hi, I am trying to calculate total cost based on a price of specific validation date:
ITEM_NO | FROM_DATE | TO_DATE | Main Price | Total QTY | Total Cost |
89163 | 18/09/2020 | 30/03/2021 | $ 9.23 | 50 | $ 461.5 |
89163 | 30/03/2021 | 01/05/2021 | $ 9.81 | 100 | $ 981.0 |
89163 | 01/05/2021 | 31/12/9999 | $ 9.66 | 150 | $ 1,449.0 |
I am after the following result:
If TODAY's date is between [FROM_DATE] and [TO_DATE], [Main Price] * [Total QTY]. Here the outcome would be the green row, $461.5 But, as soon we get to 30th of March, I would expect to get the blue row as result, $981 and so on. In this example, I have 3 different dates for the same ITEM_NO but in other cases I have 2; 4; 5 and more date ranges therefore it should be "looping" until it gets a match basically.
Also, for some ITEM_NO, [FROM_DATE] and [TO_DATE] are already in the past, if this is the case, I want to capture the latest available [Main Price].
I hope it is clear otherwhise please, let me know and I will try to explain it better.
Thanks a lot in advance,
Cheers.
Solved! Go to Solution.
Hi @alan7lp,
Try this measure. You can play around with different dates by setting the vToday variable.
Total Cost =
VAR vToday =
TODAY()
--DATE(2021, 3, 31)
VAR vCurrentItem =
MAX ( Items[ITEM_NO] )
VAR vRowInDateRange =
FILTER ( Items, vToday >= Items[FROM_DATE] && vToday <= Items[TO_DATE] )
VAR vResultInDateRange =
SUMX ( vRowInDateRange, Items[Main Price] * Items[Total QTY] )
VAR vItemMaxDate =
CALCULATE ( MAX ( Items[TO_DATE] ) )
VAR vRowWithMaxDate =
FILTER ( Items, Items[TO_DATE] = vItemMaxDate )
VAR vResultWithMaxDate =
SUMX ( vRowWithMaxDate, Items[Main Price] * Items[Total QTY] )
VAR vResult =
IF ( ISBLANK ( vResultInDateRange ), vResultWithMaxDate, vResultInDateRange )
RETURN
vResult
Proud to be a Super User!
Try this measure. There are a few differences compared to your example result, like SUPPLIER 16230 / ITEM_NO 274028 (your example shows FALSE for each row, but shouldn't it use the latest price since today isn't between any FROM_DATE / TO_DATE?).
Total Cost =
VAR vToday =
--TODAY()
DATE ( 2021, 2, 18 )
VAR vMainPrice =
MAX ( Items[Main Price] )
VAR vTotalQty =
SUM ( Items[Total QTY] )
VAR vLatestToDate =
CALCULATE (
MAX ( Items[TO_DATE] ),
ALLEXCEPT ( Items, Items[SUPPLIER], Items[ITEM_NO] )
)
VAR vLatestPrice =
CALCULATE (
MAX ( Items[Main Price] ),
ALLEXCEPT ( Items, Items[SUPPLIER], Items[ITEM_NO] ),
Items[TO_DATE] = vLatestToDate
)
VAR vNumRowsTodayInRange =
CALCULATE (
COUNTROWS ( Items ),
ALLEXCEPT ( Items, Items[SUPPLIER], Items[ITEM_NO] ),
vToday >= Items[FROM_DATE]
&& vToday <= Items[TO_DATE]
)
VAR vResult =
SWITCH (
TRUE (),
--if vToday is between FROM_DATE / TO_DATE, use current price
vToday >= MAX ( Items[FROM_DATE] )
&& vToday <= MAX ( Items[TO_DATE] ), vMainPrice * vTotalQty,
--if vToday is not between any FROM_DATE / TO_DATE, use latest price
vNumRowsTodayInRange = 0
&& MAX ( Items[TO_DATE] ) = vLatestToDate, vLatestPrice * vTotalQty
)
RETURN
vResult
Proud to be a Super User!
Hi @alan7lp,
Try this measure. You can play around with different dates by setting the vToday variable.
Total Cost =
VAR vToday =
TODAY()
--DATE(2021, 3, 31)
VAR vCurrentItem =
MAX ( Items[ITEM_NO] )
VAR vRowInDateRange =
FILTER ( Items, vToday >= Items[FROM_DATE] && vToday <= Items[TO_DATE] )
VAR vResultInDateRange =
SUMX ( vRowInDateRange, Items[Main Price] * Items[Total QTY] )
VAR vItemMaxDate =
CALCULATE ( MAX ( Items[TO_DATE] ) )
VAR vRowWithMaxDate =
FILTER ( Items, Items[TO_DATE] = vItemMaxDate )
VAR vResultWithMaxDate =
SUMX ( vRowWithMaxDate, Items[Main Price] * Items[Total QTY] )
VAR vResult =
IF ( ISBLANK ( vResultInDateRange ), vResultWithMaxDate, vResultInDateRange )
RETURN
vResult
Proud to be a Super User!
Hi mate,
Works wonders, amazing! Thanks for your help!
EDIT:
Hi @DataInsights ,
I have now discovered I have one more step to go and here my apologies as I didn't mention it before.
Currently the measure is taking into consideration ITEM_NO however I have another colum of SUPPLIER. In this case the same ITEM_NO can belong to 1 or more suppliers and have it's own price, see table below:
Current:
With your solution, I get the proper result but only if the ITEM_NO belongs to only one SUPPLIER. In the example above a correct answer should show the price based on the current logic of your solution but also considering SUPPLIER number
Wished result:
Is that possible, please?
If not clear explanation let me know as well so I try to make it clearer.
Thanks in advance,
Cheers.
Hi @alan7lp,
Here's the sample data I created, along with the result. Is this correct? If not, would you provide more accurate sample data (need multiple SUPPLIER and ITEM_NO), along with the expected result.
Proud to be a Super User!
Hi @DataInsights,
1st than anything, thanks for the time invested on helping! Much appreciated.
Above solution is close although I can see an error:
SUPPLIER 13662 ARTICLE 89163 is picking 9.81 price but the dates of that price is in the future. It should be picking 9.23 where TODAY is between those dates range.
I am sharing below an extended sample to use as data source:
SUPPLIER | ITEM_NO | FROM_DATE | TO_DATE | Main Price | Total QTY |
13662 | 89163 | 18/09/2020 | 30/03/2021 | $9.00 | 100 |
13662 | 89163 | 30/03/2021 | 1/05/2021 | $10.00 | 100 |
16230 | 89163 | 1/05/2020 | 31/12/2020 | $9.50 | 100 |
16230 | 89163 | 31/12/2020 | 1/02/2021 | $11.00 | 100 |
16230 | 274028 | 5/08/2019 | 2/09/2019 | $18.50 | 100 |
16230 | 274028 | 2/09/2019 | 26/11/2019 | $19.00 | 100 |
12879 | 274028 | 2/09/2019 | 26/11/2019 | $20.00 | 100 |
16230 | 274066 | 5/08/2019 | 2/09/2019 | $7.50 | 100 |
16230 | 274066 | 2/09/2019 | 26/11/2019 | $8.00 | 100 |
13662 | 274066 | 5/08/2019 | 2/09/2021 | $7.00 | 100 |
13662 | 274066 | 2/09/2021 | 31/12/9999 | $9.00 | 100 |
16230 | 274085 | 5/08/2019 | 2/09/2019 | $11.00 | 100 |
16230 | 274085 | 2/09/2019 | 26/11/2021 | $12.00 | 100 |
12879 | 274085 | 5/08/2019 | 2/09/2019 | $13.00 | 100 |
12879 | 274085 | 2/09/2019 | 31/12/9999 | $14.00 | 100 |
13662 | 275607 | 5/08/2019 | 2/09/2019 | $12.00 | 100 |
13662 | 275607 | 2/09/2019 | 6/01/2020 | $12.50 | 100 |
13662 | 275607 | 6/01/2020 | 24/02/2020 | $13.00 | 100 |
12879 | 275607 | 24/02/2020 | 4/05/2021 | $13.50 | 100 |
12879 | 275607 | 4/05/2021 | 18/05/2022 | $14.00 | 100 |
16230 | 275607 | 18/05/2020 | 27/07/2020 | $14.00 | 100 |
16230 | 275607 | 27/07/2020 | 31/12/9999 | $15.00 | 100 |
The expected result:
If TODAY's date is between [FROM_DATE] and [TO_DATE], [Main Price] * [Total QTY]
If TODAY's date is NOT between [FROM_DATE] and [TO_DATE], capture the latest available [Main Price] and then [Main Price] * [Total QTY]
This should take into consideration than 1 article can be shared by many suppliers. If it's shared, I should get a result per ARTICLE but also per SUPPLIER. See image below for expected outcome:
I hope this is clear enough 🙂
Thanks once again mate!
Cheers
Try this measure. There are a few differences compared to your example result, like SUPPLIER 16230 / ITEM_NO 274028 (your example shows FALSE for each row, but shouldn't it use the latest price since today isn't between any FROM_DATE / TO_DATE?).
Total Cost =
VAR vToday =
--TODAY()
DATE ( 2021, 2, 18 )
VAR vMainPrice =
MAX ( Items[Main Price] )
VAR vTotalQty =
SUM ( Items[Total QTY] )
VAR vLatestToDate =
CALCULATE (
MAX ( Items[TO_DATE] ),
ALLEXCEPT ( Items, Items[SUPPLIER], Items[ITEM_NO] )
)
VAR vLatestPrice =
CALCULATE (
MAX ( Items[Main Price] ),
ALLEXCEPT ( Items, Items[SUPPLIER], Items[ITEM_NO] ),
Items[TO_DATE] = vLatestToDate
)
VAR vNumRowsTodayInRange =
CALCULATE (
COUNTROWS ( Items ),
ALLEXCEPT ( Items, Items[SUPPLIER], Items[ITEM_NO] ),
vToday >= Items[FROM_DATE]
&& vToday <= Items[TO_DATE]
)
VAR vResult =
SWITCH (
TRUE (),
--if vToday is between FROM_DATE / TO_DATE, use current price
vToday >= MAX ( Items[FROM_DATE] )
&& vToday <= MAX ( Items[TO_DATE] ), vMainPrice * vTotalQty,
--if vToday is not between any FROM_DATE / TO_DATE, use latest price
vNumRowsTodayInRange = 0
&& MAX ( Items[TO_DATE] ) = vLatestToDate, vLatestPrice * vTotalQty
)
RETURN
vResult
Proud to be a Super User!
Works perfectly!
Thanks mate!
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 |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |