Search code examples
daxcalculated-columnspowerpivot

DAX calculate previous value also when it doesn't exist

I have data that collects "snapshots" of a Warehouse. It has three columns: SnapshotDate, Item, OnHand

(Real data has nearly 100.000 records for each SnapshotDate, here I post an example)

enter image description here

I want to measure the Delta OnHand with respect to the previuos Date (whatever it is).

So I've set three calculated columns:

PreviousDate

=
var currentDate = Inventory[SnapshotDate]
return CALCULATE( MAX( Inventory[SnapshotDate]); 
FILTER( ALL(Inventory); Inventory[SnapshotDate] < currentDate)
)

PreviousOnHand

= LOOKUPVALUE(Inventory[OnHand]; Inventory[SnapshotDate]; Inventory[PreviousDate]; Inventory[Item]; Inventory[Item])+0

OnHandDelta

=Inventory[OnHand]-Inventory[PreviuosOnHand]

Delta measure:

Delta:=SUM(Inventory[OnHandDelta])

When I pivot this data, I'd like to see 0 (red bordered cell) when the data doesn't exist and more important I should see the Delta value -10 (cyan cell).

enter image description here

How could this be achieved?

Link to the test Excel file

Solution

  • The issue here is that combination of date / item (15/04/2019, Item C) simply does not exist in your fact table. So it makes no difference what measure you create, it will never be calculated.

    One approach is to create separate dimension tables for Date and Item, loaded to data model, with relationships to your fact table.

    Query Date Dimension:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Grouped = Table.Sort(Table.Group(Source, {"SnapshotDate"}, {}), "SnapshotDate"),
    #"Changed Type" = Table.TransformColumnTypes(#"Grouped",{{"SnapshotDate", type date}})
in
    #"Changed Type"

    Query Item Dimension:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Grouped = Table.Sort(Table.Group(Source, {"Item"}, {}), "Item"),
    #"Changed Type" = Table.TransformColumnTypes(#"Grouped",{{"Item", type text}})
in
    #"Changed Type"

    enter image description here

    Now you can create what you want as MEASURES, with no calculated columns needed:

    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]

    Now using dimensions from the Dimension tables in your pivot, with the Delta measure:

    enter image description here

    Updated XLSX file: https://excel.solutions/so_55740804/