Forum Discussion

andreame's avatar
andreame
Frequent Visitor
2 years ago

Measure including Cumulative and Non-Cumulative Values

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...
DallasBaba's avatar
DallasBaba
Icon for Skilled Sharer rankSkilled Sharer
2 years ago

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's avatar
    andreame
    Frequent Visitor
    2 years ago

    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]