Running Total calculation based on criteria

[EnderWiggin]

Hi All,

I would like to ask your help to solve to following running total calculation problem as it is clearly above of my DAX knowledge.

Please, see this PBI file link for more details: RunningTotalDemo 

I want to use running total from the actual month(presently 202107 and provided by m_ActualMonth measure) which base value is calculated on the following way:

ActualStock.Quantity + IODiff of 0BeforeActual + IODiff of actual month(202107). In other cases only the IODiff is counted. This calcualtion is provided by m_StockIODiffForRunningTotal measure:

m_StockIODiffForRunningTotal = 
VAR ActualMonthValue =
    SUM ( Material[ActualStock.Quantity] )
        + CALCULATE (
            SUM ( StockInputOutput[IODiff] ),
            StockInputOutput[YearMonthAndBefore] = "0BeforeActual"
        )
        + SUM ( StockInputOutput[IODiff] )
VAR Result =
    IF (
        MIN ( StockInputOutput[YearMonthAndBefore] ) = [m_ActualMonth],
        ActualMonthValue,
        SUM ( StockInputOutput[IODiff] )
    )
return Result

The first matrix shows the IODiff and the second shows the calculated running total in the value area:

The calculation of running total is ok, but there are some flaws, please see the color signs on the picture.
green: in case if the actual month(202107) as value then the m_StockIODiffForRunningTotal provides the correct value
red: if no value in the actual month, the m_StockIODiffForRunningTotal doesn't work
orange: the 0BeforeActual column is filtered because of the running total formula but it should be visible in the matrix.

My questions are:
- how can be calculated the "ActualStock.Quantity" + IODiff of 0BeforeActual if the actual month(202107) has no any value?
- how it is possible to calculate running total from the calculated actual month(202107) and show the 0BeforeActual column in the visual at once? Perhaps it is not possible if I use FILTER function in the running total measure:

m_RunningTotal = 
calculate(
[m_StockIODiffForRunningTotal],
FILTER(
ALL(StockInputOutput[YearMonthAndBefore]),
StockInputOutput[YearMonthAndBefore] >= [m_ActualMonth] && StockInputOutput[YearMonthAndBefore] <= max(StockInputOutput[YearMonthAndBefore])))

Thank you very much in advance!

[lbendlin]

Your data model is missing a Calendar/Dates table.  If you add that then you can improve your measures to gracefully handle situations where there is no data for a particular material and month.

[EnderWiggin]

Hi @lbendlin,

sorry for the late response. Yes, you are right, meanwhile I created a dynamic calnedar table based on the min/max year/month values of StockInputOutput (I added a date field to this table whic is the first day of the month e.g 202107 -> 2021.07.01) and set the connection betwwen the two tables based on dates. So it was missing to show running in the matrix continously. 

Thank you for your feedback!