Calculated Field using sum of one column but max of another

[Anonymous]

Hi all,

I am trying to figure out how to calculate how many weeks of supply of product I have based on my sales and the 'ending on hand' I have currently.

The problem comes with pivots always combining the data in the 'ending on hand'

I have data from several weeks being combined in the Data Model.

Example

past 3 weeks

Week 1

Item | Sales | EOH

A        |    3       |    7

B         |    2       |    4

Week 2

A    |  3  |  4 (Week 1 EOH - Week 2 Sales)

B    |   2 |  2

Formula: EOH (most current week) / (Sum of Sales/Count of weeks with sales (disctinct count))

[dax]

Hi hrojas3,

I am not clear about your requirement, what did you mean "The problem comes with pivots always combining the data in the 'ending on hand'"? If possible, could you please explain it in details? In addition, if possible, could you please inform me your sample data and expecting output? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

[Anonymous · Accepted solution]

I was able to do it using CALCULATE

EOP =
CALCULATE (
    SUM ( [EOP Inv Qty] ),
    FILTER ( Weekly, [Fiscal Week Number] = MAXX ( Weekly, [Fiscal Week Number] ) )
)