Get earliest price based on date

[danashton84]

Good afternoon all,

As an example i have the following data:

Material price History

Date | Material Cost

23/05/2022 | £1

24/05/2022 | £2

19/08/2022 | £2.50

Consumptions GB

Consumption Date  | Produced M2

01/05/2022 | 100

25/05/2022 | 200

25/08/2022 | 300

I am trying to calculate the produced m2 x material cost, for example I want to look at the record on 25/05, and it should be 200 x £2 as the record on the 24/05 is the latest date before the consumptions.

Another example, I want to do the cost for 24/08 so 300 x (I would expect it to get the value of £2.50).

I hope this make sense, i've tried doing things like calculate with lastdate but am getting no where.

Thanks

[danashton84]

Sorry to chase but does anyone have any ideas on this?

Thanks. Dan

[v-zhangti · Accepted solution]

Hi, @danashton84 

You can try the following methods.

Column:

Lastdate =
CALCULATE (
    MAX ( Material[Date] ),
    FILTER ( Material, [Date] < EARLIER ( Consumptions[Consumption Date] ) )
)

Cost =
VAR MaterialCost =
    CALCULATE (
        MAX ( Material[Material Cost] ),
        FILTER ( Material, [Date] = EARLIER ( Consumptions[Lastdate] ) )
    )
RETURN
    MaterialCost * [Produced M2]

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danashton84]

Afternoon

Sorry to be a pain but in the below 

Lastdate =
CALCULATE (
    MAX ( Material[Date] ),
    FILTER ( Material, [Date] < EARLIER ( Consumptions[Consumption Date] ) )

 The consumptions table and materials tables are different so although the logic is correct, it will not run - see below 

Lastdate =

CALCULATE (

    MAX ( 'Material Price History'[Date] ),

    FILTER ( 'Material Price History', [Date] < EARLIER (Consumptions Good Or Bad[Consumption Date] ) )

)

Microsoft error is 'The following syntax error occurred during parsing: Invalid token, Line 4, Offset 56, 'C))))))).'

Also tried 

Lastdate =

CALCULATE (

    MAX ( 'Material Price History'[Date] ),

    FILTER ( 'Material Price History', [Date] < EARLIER ('Consumptions Good Or Bad'[Consumption Date] ) )

)

But still same issue.

Any ideas? Thanks. Dan