Need help: Cumulative movement based on measures; results are messed up!

[vishal_maverick]

Hi

I am trying to calculate the 6 months cumulative movement of inventory. Data comes from multiple transaction tables and each line you see is a measure.

I am giving below some key measures which are coming into play:

Expected Receipts = calculate(max([DOK Receipts],([Open PO]+[Incoming Transit])))

Expected Dispatches = calculate([Confirmed SO]+[Unconfirmed SO]+[Consignment TO],'Calendar'[Horizon]="Current Month")+CALCULATE(max([Forecast],([Confirmed SO]+[Unconfirmed SO]+[Consignment TO])),'Calendar'[Horizon]<>"Current Month")

Expected Consumption = calculate([OPS TO],'Calendar'[Horizon]="Current Month")+CALCULATE(max([OPS Consumption (Plan)],[OPS TO]),'Calendar'[Horizon]<>"Current Month")

Movement = [Expected Receipts]-[Expected Dispatches]-[Expected Consumption]

Cumulative Movement = calculate([Movement], FILTER(ALLSELECTED('Calendar'), 'Calendar'[Date]<=MAX('Calendar'[Date])))

Upto calculation of Movement per month, everything is fine. Cumulative Movement goes well upto first 2 months but then the problem begins. So far I could dignose that there is a certain pattern:

• In the 3rd month, confirmed sales orders exceeded the forecast. so Expected dispatch is correctly calculated. But the cumulative movement formula did not consider it. It went by considering the forecast number. Therefore, cumulative number shows -40507, which should have been -72532. This gives error of 32025.
• in the 4th month, DOK receipts are higher than the incoming transit+Open purchase orders. Expected receipts were calculated correctly. But the cumulative formula ignored that. It has gone ahead with the total of incoming transit + Open purchase orders, i.e., zero. Thus the cumulative movement shows -334261 instead of 130766. This is explained as a gap of 235520 (from the receipt side error of this month) + the 32025 error carried forward from the previous month... total cumulative error of 203495.

Can someone tell me what I am doing wrong?

[v-cazheng-msft]

Hi @vishal_maverick,

Could you please provide some sample data used in your measures and let me know how you build relationships for them if you still have problems on it? Thanks in advance!

Best Regards,

Community Support Team _ Caiyun

[vishal_maverick]

Thanks for the response. the problem is i am dealing with loads of transactional data and multiple relationships between the tables. I am unable to share the data. 

Considering all measures are working out well until calculation of monthly movement, i dont see a problem in those measures. It is only the final one.