Calculate Projected Inventory for next 12 months

[sfranchi]

Hi All,

I am new in the power bi world and hoping someone can help me. 

I've been trying to calculate a Projected Inventory for next 12 months for the last 2 weeks without any luck 

Data is based on input from the MRP (D365 - Supply Schedule). I am trying to replicate the same concept in D365 – Supply Schedule where users can view at high level and detail level.

Key Points;

1. Previous date (01/01/1900) will be used to determine yesterday opening, net and closing qty

2. Opening Qty = sum(On hand)

3. Net Qty =Qty Receipt + Qty Issue 

4. Closing Qty = Opening Qty + Net Qty. This will be opening qty for next month regardless if there is transaction on that particular month. 

5. If there is no net qty for particular month, opening and closing will be the same as per closing value of last month.

6. Need to calculate extended cost for point 2,3,4.

7. I only provided a sample of data. I am handling more than 250K to 500K data

DAX Calculation

      Cal Opening Qty = sum(Requirement[On Hand]) +

                                      CALCULATE(sum(Requirement[On Hand]) + sum(Requirement[Net Qty]),

                                             FILTER(ALLEXCEPT(Requirement,Requirement[ItemIDKey],Requirement[dataAreaId] ),

                                             Requirement[Month] < MAX(Requirement[Month])))

      Net Qty = sum(Requirement[net qty)

 Cal Closing Qty = sum(Requirement[On Hand]) +

                                      CALCULATE(sum(Requirement[On Hand]) + sum(Requirement[Net Qty]),

                                             FILTER(ALLEXCEPT(Requirement,Requirement[ItemIDKey],Requirement[dataAreaId] ),

                                             Requirement[Month] < MAX(Requirement[Month])))

      Extended Cost calculates by multyply above with cost

Outcomes;

Issues;

1. The opening qty does not carry over to next month due to no transactions (highlighted in yellow)

    If there is no net qty, the opening and closing qty should be the same as per closing value of last month.

2. Opening, net and Closing value are calculated in-correctly (wrong). The calculation for Qty seems OK

3. Total value is incorrect.

The value should be

I wanted to create a summary table by

1. AU/NZ

2. Vendor

3. Item level

Can someone please help me especially in DAX formula?

Cheers

SF

[v-lili6-msft]

hi, @sfranchi 

It seems that the link doesn't work, please check it

That didn't work

We're sorry, but you can't be found in the nhp365-my.sharepoint.com directory. Please try again later, while we try to automatically fix this for you.

Best Regards,

Lin

[sfranchi]

Hi Lin (@lin),

It seems OK for me to downloadload the pbix but saying that i might have access to my one drive. I believe i need to give permission to my folder or do you have alternative that i could send the file?

Cheers

SF

[sfranchi]

Hi Lin (@v-lili6-msft)

Try this link;

Pbix - Projected Inventory (Sample Data)

Please let me kn ow if it does not work.

Thanks

SF

[v-lili6-msft]

hi, @sfranchi 

1. You could try this formula as a new logic

New Cal Closing Qty = var _predate= CALCULATE(MAX(Requirement[Month]),FILTER(ALLEXCEPT(Requirement,Requirement[ItemIDKey],Requirement[dataAreaId]),Requirement[Month]<MAX('Calendar'[Date]))) return
IF(ISBLANK(SUM(Requirement[Net Qty])), CALCULATE([Cal Closing Qty],FILTER(ALLEXCEPT(Requirement,Requirement[ItemIDKey],Requirement[dataAreaId]),Requirement[Month]=_predate)),[Cal Closing Qty])

But this will calculate for each month in Calendar table, for example: 2019/07 2019/08 2019/09... you must filter it or add a filter for it

2. Qty and Value have the same logic in your measure, what wrong with it.

3. This looks like a measure totals problem.  See this post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Best Regards,

Lin

[sfranchi]

Hi @v-lili6-msft 

Thanks for the formula. I used this formula to calculate Opening and Closing Qty (see below image)

The output;

The formula seems working. However the closing qty does not calulcate correctly.

Manual Calculation (correct answer)

Prior April 2019;

      a. AU: Closing Qty: 4,295,670 + 391,474 = 4,687,144 (not 4,686,964)

      b. NZ: Closing Qty: 433,465+38,818 = 472,283 (not 472,283)

      c. Total: 5,159,427 (not 5,159,246)

When i filtered the report by item level, it's calculated correctly but not at Country level or summary level. Any suggestion what the issues are? 

I then went to the suggested website (Measure Totals - The Final Words). I tried all combinations how to calculate the total but i have no luck. One of the samples is as follows

The output

Prior April 2019, the new Cal Close 2 figure is correct. Verry Happy. I thought i solved the issue.

However it stopped there. April, May and so on, the Cal Close 2 is incorrect.

Are you able to share some light regarding this matter? 

Many thanks in advance

Regards

SF

[sfranchi]

Hi @v-lili6-msft 

Are able to help me regadring the Total Qty (see below request)?

Also, i am struggling when i tried to calculate the Inventory Value

I used the same logic as in Qty;

Above formula should give me cost for all items by months. If i just used [WAC] it only calculate when there is a data.

[Wac] is weighted average cost from other table

I then used [cal avg cost] * [cal closing qty final]. It took long time to compute and eventually failed.

What did i do wrong? It's very frustating

Anyone can help!!

Thanks SF

[sfranchi]

Hi @v-lili6-msft 

Thanks for formula. I used the formula that you gave to calculate Opening and Closing Qty. The details are

Output:

These formula seem working at item level (when i filtered only one item :).

However the closing Qty does not add up correctly at Country Level.

Manual calculation

Prior April 2019; Closing Qty should be

            AU: 4,295,670 + 391,474 = 4,687,144 (Not 4,686,964).

            NZ: 433,465 + 38,818 = 472,283 (Not 472,282)

            Total: 5,159,427 (Not 5,159,246)

It's also happnening for next month and so on

I went to the website that you provided (to calculate total) - Measure Total, Final Words.

I tried different combinations yesterday and today but i have no luck. 

The output is

I was so happy when i saw closing Qty (Cal Close 2) prior April 2019 is correct but it stopped there.

April, May, and so on are in correct.

Can you please help me regarding this matter?

Many thanks in advance 

Regards

SF