I have data that collects "snapshots" of a Warehouse.
The data has three columns: SnapshotDate
, Item
, OnHand
I have defined these measures on data:
Measure On Hand Current:
On Hand Current:=
SUM ( Inventory[OnHand] ) + 0
Measure On Hand Previous:
On Hand Previous:=
VAR DateCurrent =
IF (
HASONEVALUE ( 'Date Dimension'[SnapshotDate] ),
VALUES ( 'Date Dimension'[SnapshotDate] ),
BLANK()
)
VAR DatePrevious =
CALCULATE (
MAX ( 'Date Dimension'[SnapshotDate] ),
FILTER (
ALL ( 'Date Dimension' ),
'Date Dimension'[SnapshotDate] < DateCurrent
)
)
RETURN
CALCULATE (
[On Hand Current],
FILTER (
ALL ( 'Date Dimension'[SnapshotDate] ),
'Date Dimension'[SnapshotDate] = DatePrevious
)
) + 0
Measure Delta:
Delta:=
[On Hand Current] - [On Hand Previous]
Preamble: in my “real” situation, I have nearly 99% of items that are not handled from a date to the next. Since I am mostly interested to measure the Delta OnHand from a date to the next, I want to hide Items that have no differences. For example (see screenshot below), I’d like items K and X to disappear from the pivot table.
To do this, I uncheck the first date (01/04/2019) from the “Columns Labels” and I add a filter on the OnHandCurrent
using the Excel filter “Greater than…”.
The row with item K doesn’t disappear!