Measure including Cumulative and Non-Cumulative Values

[andreame]

Hi,

I want to create a simple Inventory Forecast. The idea is to visualize in a Matrix the stock projection for every item#.

The inventory forecast should include the following 4 values:

Inventory = Current Inventory + Current Cargo - Demand Plan + Open Purchase Orders

Demand Plan and Open Purchase Orders needs to be rolling sums, while Current Inventory needs to have no date filter in the calculation. This is because the Inventory is calculated as the sum of all postings of an item. So the current stock needs to be summed in all future months calculations.

The demand plan and open Purchase orders are already working. I created calculated columns for cumulative demand plan and open orders. My problem is that the current inventory is not getting summed up for future months. 

Example:

Oct 2023: Stock(today) - Demand(October 2023) + Purchase Orders(October 2023)

Nov 2023: Stock(today) - Demand(October+Novemner) + Purchase ORders(October+November 2023)

How can I create this measure?

Inventory = Calculate(SUM(Artikelposten[Menge]),ALL(Artikelposten[Buchungsdatum]),FILTER(Artikelposten,Artikel[Artikel Nr.]=Artikelposten[Artikelnr.] && Artikelposten[Lagerort]="ZENTRAL"))

Demand Plan = SUMX(FILTER('Components Plan','Components Plan'[Artikel]=EARLIER('Components Plan'[Artikel])&&'Components Plan'[Date]<=EARLIER('Components Plan'[Date])&&'Components Plan'[Date]>=DATE(Year(today()),MONTH(TODAY()),1)),'Components Plan'[Value])

Running Open PO = SUMX(FILTER('Purchase Lines','Purchase Lines'[no]=EARLIER('Purchase Lines'[no])&&'Purchase Lines'[expectedReceiptDate]<=EARLIER('Purchase Lines'[expectedReceiptDate])),'Purchase Lines'[outstandingQuantity])

Cargo Amount =

SUMX(

    FILTER(

        'Purchase Lines',

        'Purchase Lines'[locationCode] = "Cargo" && 'Purchase Lines'[cargoStatus] <> "Ankunft" && 'Purchase Lines'[cargoStatus] <> ""

    ),

    'Purchase Lines'[quantity]

)

Thanks!

[andreame]

Hi @DallasBaba, I tried to create your measures but doing the first (Rolling Demand Plan), I have the following error:

Let me know if you can help me there. I don´t think that measure is possible

[DallasBaba]

@andreame you can fix the rolling demand plan error is this messaure 

Rolling Demand Plan =
VAR SelectedDate = MAX('Components Plan'[Date])
VAR StartOfMonth = DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

RETURN
CALCULATE(
    SUM('Components Plan'[Value]),
    FILTER(
        ALL('Components Plan'),
        'Components Plan'[Date] <= SelectedDate
        && 'Components Plan'[Date] >= StartOfMonth
    )
)

To include the Rolling Purchase Orders, Cargo Amount, and Reserved Quantities in your Inventory Forecast formula, you can create new measures for each of them

Rolling Purchase Orders =
VAR CurrentDate = MAX('Components Plan'[Date])

RETURN
CALCULATE(
    [Running Open PO],
    FILTER(
        ALL('YourDateTable', 'Components Plan'),
        'YourDateTable'[Date] <= CurrentDate
        && 'YourDateTable'[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
    )
)

Cargo Amount =
CALCULATE(
    SUMX(
        FILTER(
            'Purchase Lines',
            'Purchase Lines'[locationCode] = "Cargo"
            && 'Purchase Lines'[cargoStatus] <> "Ankunft"
            && 'Purchase Lines'[cargoStatus] <> ""
        ),
        'Purchase Lines'[quantity]
    ),
    ALL('YourDateTable', 'Components Plan') // Use a relevant date table
)

Reserved Qty =
Artikel[qtyOnComponentLines] + Artikel[qtyOnSalesOrder] + Artikel[qtyOnAsmComponent]

If the Reserved Qty do not depend on a date, you can directly use the measure in your Inventory Forecast formula.

Inventory Forecast =
VAR CurrentMonth = MAX('Components Plan'[Date])

RETURN
[Current Inventory] + [Rolling Purchase Orders] + [Cargo Amount] - [Demand Plan] + [Reserved Qty]

I hope this approach meets your needs. But if not, please share a pbix sample dataset.

@ me in replies or I'll lose your thread!!!

Thanks

[andreame]

Hi @DallasBaba ,

I tried this. The Rolling Demand Plan you suggested doesn´t work because I lose the filter on Item# level. Using the ALL I remove all the filters, therefore I am not able to see it by item# level.

Do you have a solution for this?

Thanks,

Andrea

[DallasBaba]

@andreame you can modify the measure to keep the filter on the Item# level intact. 

Rolling Demand Plan =
VAR SelectedDate = MAX('Components Plan'[Date])
VAR CurrentItem = EARLIER('Components Plan'[Artikel])  // Preserve the Item# filter

RETURN
CALCULATE(
    SUM('Components Plan'[Value]),
    FILTER(
        ALL('YourDateTable', 'Components Plan'),
        'YourDateTable'[Date] <= SelectedDate
        && 'YourDateTable'[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
        && 'Components Plan'[Artikel] = CurrentItem  // Preserve the Item# filter
    )
)

By using EARLIER('Components Plan'[Artikel]) within the FILTER, you ensure that the filter on the Item# is maintained, and the Rolling Demand Plan will be calculated correctly for each specific Item#. 

[DallasBaba]

@andreame To create a measure that calculates the Inventory Forecast as described, you'll need to create three new measure

1-Rolling Demand Plan
2-Current Inventory
3-Inventory Forecast

Rolling Demand Plan =
VAR SelectedDate = MAX('Components Plan'[Date])

RETURN
CALCULATE(
SUM('Components Plan'[Value]),
FILTER(
ALL('YourDateTable', 'Components Plan'),
'YourDateTable'[Date] <= SelectedDate
&& 'YourDateTable'[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
)
)

------------- Next

Current Inventory =
CALCULATE(
SUM(Artikelposten[Menge]),
ALL(Artikelposten[Buchungsdatum]),
FILTER(
Artikelposten,
Artikel[Artikel Nr.] = Artikelposten[Artikelnr.]
&& Artikelposten[Lagerort] = "ZENTRAL"
)
)

-------------------------- Next

Inventory Forecast =
VAR CurrentMonth = MAX('Components Plan'[Date])

RETURN
CALCULATE(
[Current Inventory], // Using the above measure for current inventory
FILTER(
ALL('Components Plan'),
'Components Plan'[Date] >= CurrentMonth
)
) + [Running Open PO] - [Demand Plan]

You can try the above measures, and let's see if it works. If not, I want you to decompose your data model into 3rd-level normalization for a star schema.

1- Break your model down to a Fact and dimensional model
2- Ensure you only have the data you need for this report
3- Ensure you don't have any column with the same set of data available in your model in two tables unless is PK or FK

See below screenshort on how your data model should look like.  

@ me in replies or I'll lose your thread!!!

Thanks

Dallas

[andreame]

Hi @DallasBaba, thanks so much for your reply!

I have a small question before. My inventory forecast formula is composed by:

1) Current inventory: you provided me the new formula

2) Rolling demand plan: You also sent me new formula

3) Rolling purchase orders: This you didn´t give me a new formula. Is this okay to keep it as a column?

3) I would have also 2 additional parts in the formula. One is the Cargo amount. Needs to be considered same as Current Inventory, so a value that I have now that I will sum to future months. At the moment is calculated like this:

Cargo Amount =

SUMX(

    FILTER(

        'Purchase Lines',

        'Purchase Lines'[locationCode] = "Cargo" && 'Purchase Lines'[cargoStatus] <> "Ankunft" && 'Purchase Lines'[cargoStatus] <> ""

    ),

    'Purchase Lines'[quantity]

)

And the last thing also to be considered same as inventory is the reserved quantities, at the moment calculated like this. The reserved quantities don´t have a date in the table, they are just a value associated to every item number.

Reserved Qty = Artikel[qtyOnComponentLines]+Artikel[qtyOnSalesOrder]+Artikel[qtyOnAsmComponent]