Matrix visual mystery on running totals...

[vpsoini]

Hello.

I'm really puzzled with matrix visual trying to show the running total value over time.  In the attached picture you can see two similar matrix visuals (with slighlty different measure as value) and a graph visual about same value.  My running total (inventory change over time) is correctly calculated, but i also needed the value to take into account the start value, which was not zero (here it was 6)

The upper matrix visual in the picture shows [ground value] + [running total] and the lower matrix only shows [running total]. The graph visual shows [ground value] + [running total].  As comparison there's a snip from excel showing the inventory evolution per dates. 

The big mystery for me is the upper matrix, which should show the inventory over time (same as the graph visual). For some reason, it shows only correct value on dates, where the running total actually changes (except on 24.1. when it has the wrong number and 15.1. when it changes without obvious reason?). In between the "change dates" it shows the running total change (missing the start value, which is here 6).

The running total is calculated fron 2 tables (sales and purchases) and the variation is correct in lower matrix. Ground value is taken from items-table, and it has only one value per item (not date related). These tables are linked with item numbers between each others.

The base question is, why is the same measure as value working OK in graph visual, but not in matrix visual? A bug?

Any help is highly appreciated again... 😊

[MFelix]

Hi @vpsoini ,

This has to do with context and the way you are calculating your measure.

Since the 6 does not have any dates it is being constantly added to your calculations when you get to values with 6. In matrix and other visualizations you need to be carefull because adding fields changes the context of the measures and when you have calculations like the begining inventory this can cause errors.

Can you please share the measure you are using for this calculation?

[vpsoini]

Hi @MFelix 

The measure looks like this:

RunningTotal = MAX(Items[Inventory]) + CALCULATE(SUM('Item ledger'[Quantity]),FILTER(ALLSELECTED('Item ledger'),'Item ledger'[Posting_Date] <= MAX('Item ledger'[Posting_Date])))

Where Items - table has the item number, name and base value (inventory), where Item ledger has Item number, Quantity (change +/-) and Posting date and these tables are linked with Item number fields

[MFelix · Accepted solution]

Hi @vpsoini ,

Believe your issue is because you don't have consecutive dates on your calculation for a specific item, altough all your products at some point have values making your dates consecutive for a specific item that is not true so your calculation breaks when you have no results.

• Create a calendar table with unique values and starting in 1st January your minimum year and ending at 31st December your maximum year
• Change your measure to:

RunningTotal =
SUM ( Items[Inventory] )
    + CALCULATE (
        SUM ( 'Item ledger'[Quantity] ),
        FILTER (
            ALL ( 'calendar'[Date] ),
            'calendar'[Date] <= MAX ( 'calendar'[Date] )
        )
    )

Small model attach.