Running Stock Levels - remove row context

[Anonymous]

Hello,

I have an issue with a running total measure whereby the value displayed on the maxtrix visual for a row conflicts with the filter applied to the running total in the filters pane, e.g:

- running total for a product on a given date = -1

- set filter panel on the visual to show items where running total < 0

- the correct row for the product/date appears but the running total = 4

the rows are filtered correctly but when the running total is evaluated for each row, it only considers the rows returned by the filter context.

here is the running total measure :

Running Total =

CALCULATE(

    [Stock Movement],

ALLSELECTED('Dim Date'),

    FILTER(

        ALLSELECTED('Dim Date'[Date]),

        'Dim Date'[Date] <= MAX('Dim Date'[Date])

    )

) + [Stock Quantity]

What can I add to the measure to remove this behaviour?

[v-henryk-mstf]

Hi @Anonymous ,

Can you provide some test data (delete sensitive information), I will answer further. Looking forward to your reply.

Best Regards,
Henry

[lbendlin]

Your measure seems to have extra dangly bits, and can be streamlined.

Running Total =
CALCULATE(
    [Stock Movement],
ALLSELECTED('Dim Date'),
'Dim Date'[Date] <= MAX('Dim Date'[Date])
) + [Stock Quantity]

However, your MAX function is evaluated in the row context, so it is likely not doing what you want it to do.  Most likely you want to have a different calculation for that value (in a variable)  and then use the variable in the filter.

Running Total =
var m=CALCULATE(max('Dim Date'[Date]),<some filter>)
Return CALCULATE(
    [Stock Movement],
ALLSELECTED('Dim Date'),
'Dim Date'[Date] <= m
    )
) + [Stock Quantity]

[Anonymous]

Hi @lbendlin 

Thanks for the reposonse. Yup some dangly bits, they were products of previous attempts, thanks for suggesting a refined version though.

How can I filter the max date whereby the measure uses the max date for each row of the table/matrix but still adds all rows previous to the date? 

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

It may be an error caused by the relationship between the tables, and no further testing can be done temporarily.

Refer to the links below to provide corresponding information. How to Get Your Question Answered Quickly - Microsoft Power BI Community

Best Regards,
Henry