Total cost per validity date

[alan7lp]

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.

[DataInsights · Accepted 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

[DataInsights · Accepted 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

[alan7lp]

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.

[DataInsights]

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.

[alan7lp]

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

[DataInsights · Accepted solution]

@alan7lp,

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

[alan7lp]

Works perfectly!

Thanks mate!