Issue pulling previous to current month item quantities

[Anonymous]

Hello everyone,

today I encountered an issue with a calculation that I do not quite understand. A customer asked me to create a graph and calculation for inventory movements, the goal is for monthly data of inventory data, including stock level, safety stock, ordered stock and forecasted demand. The inventory level = previous month inventory + previous month purchases - previous month forecast.

For completeness, this is the data model:

Now my issue is in pulling the previous month incoming items (production and purchase), while getting the demand with a very similar Measure went quick and easy. We're looking at monthly data, and retreiving the previous month's data I do via a YearMo (YYYYMM, hier JahrMonat) integer on the date table dimCalendar, which I added a ranking (the further into the future, the lower the value with the furthest future YearMo being 1.

First, here's the working Demand measure:

msrDemandGraph = 
    CALCULATE(
        SUM(DemandForecastsV2[ForecastedQuantity]),
        FILTER(
            ALL(dimCalendar),
            dimCalendar[Rank] = LOOKUPVALUE(dimCalendar[Rank], dimCalendar[Date], MAX(dimCalendar[Date])) + 1
                && dimCalendar[Date] >= TODAY()
        )
    )

However, the very similar Measure for the incoming goods via purchase and production does not work, the difference is that I have to additionally filter for specific reference types.

msrOrderStockGraph = 
    CALCULATE(
        SUM(ReqStaticPlanProductionOrderScheduleEntity[Qty]),
        FILTER(
            ALL(dimCalendar),
            dimCalendar[Rank] = LOOKUPVALUE(dimCalendar[Rank], dimCalendar[Date], MAX(dimCalendar[Date])) + 1
                && dimCalendar[Date] >= TODAY()
        ),
        FILTER(
            ReqStaticPlanProductionOrderScheduleEntity,
            (ReqStaticPlanProductionOrderScheduleEntity[RefType] = "Purch"
                || ReqStaticPlanProductionOrderScheduleEntity[RefType] = "Production"
                || ReqStaticPlanProductionOrderScheduleEntity[RefType] = "ProdLine")
        )
    )

Now this results in the following data, and I marked the spot where I was expecting an offset value (item arrival and item demand are on separate locations, hence the line separation):

Now when I check both of the FILTER functions in the not working msrOrderStockGraph, I get the correct data. The first filter works as is also proven by the working Demand measure. The second filter returns the right row (in this case there's only one for those 40.000 units).

I eventually got to a correct solution, namely in the following formula, but I would still like to understand why the measure I posted above does not get me the right results, just the same as the demand function does... Can anyone explain to me what I am missing here? Suggestions as to better/faster ways to get to the result are welcome, too!

msrOrderStockGraph = 
    CALCULATE(
        SUM(ReqStaticPlanProductionOrderScheduleEntity[Qty]),
        FILTER(
            ALL(dimCalendar),
            dimCalendar[Rank] = LOOKUPVALUE(dimCalendar[Rank], dimCalendar[Date], MAX(dimCalendar[Date])) + 1
                && dimCalendar[Date] >= TODAY()
        ),
        FILTER(
            ALL(ReqStaticPlanProductionOrderScheduleEntity),
            (ReqStaticPlanProductionOrderScheduleEntity[RefType] = "Purch"
                || ReqStaticPlanProductionOrderScheduleEntity[RefType] = "Production"
                || ReqStaticPlanProductionOrderScheduleEntity[RefType] = "ProdLine")
            && ReqStaticPlanProductionOrderScheduleEntity[ItemLocation] = MIN(dimItemLocation[ItemLocation])
        )
    )

[Anonymous · Accepted solution]

Hi @Anonymous ,

The top formula works, but it only works for the row. You add All at the next formula so you got the result you wanted. There are some difference between the measures and columns.

An important difference between measures and calculated columns is that measures are evaluated in the filter context of the visual in which they are applied. The filter context is defined by the filters applied in the report such as row selection, column selection, report filters and slicers applied. Measures are only evaluated at the level of granularity they are plotted at. As calculated columns are computed when you first define them/ when you refresh your dataset, they do not have access to the filter context. Calculated columns are calculated outside of the filter context and do not depend on user interaction in the report.

More details : What is the difference between a measure and a calculated column? 

I have also found a useful blog, please refer to it to see if it helps you.

When to Use Measures vs. Calc Columns 

 Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.