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

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

    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#.