calculation based on 2 tables with calendar

[mhsk]

i'm having trouble creating a measure to show me calculation from 2 tables.

My model looks like this:

What i would like to achieve is to take starting_QOH (there is just one starting_QOH in in_mstr under in_part) and decuct from this starting_QOH demand for each week and each item and add open PO for each part for each week.

so my expected result is as below (mind i have many of in_part):

i can catch the starting value to make the calculation, would you help?

[v-shex-msft]

HI @mhsk ,

If you can please share some sample data for test, it is hard to test without any sample data.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[mhsk]

Hi, pbix file can be found here:

https://app.box.com/s/jlhtu79kzbmfwkcs018olv5tg43vjfis

Please do not apply changes on query all I have is already imported into the model, the goal is to get Quantity on Hand value for each item and minus it with demand and plus with supply for each week. For next week start should be the previous week end value. @v-shex-msft Thanks in advance for suggestions and feedback.

[mhsk]

any ideas? what im looking for is a rolling calculation with set start and change week by week

[v-shex-msft]

Hi @mhsk ,

You can use following measure if it suitable your requirement:

Measure =
VAR currDate =
    MAX ( ActualDate[Date] )
RETURN
    SUM ( 'Quantity on Hand'[QOH] )
        - CALCULATE (
            SUM ( demand[Demand QTY] ),
            FILTER ( ALLSELECTED ( ActualDate ), [Date] <= currDate )
        )
        + CALCULATE (
            SUM ( supply[Open PO] ),
            FILTER ( ALLSELECTED ( ActualDate ), [Date] <= currDate )
        )

Regards,

Xiaoxin Sheng