Calculate measure involving dates on a factTable with DAX

Question

I have this problem: given a "Movements" factTable that holds a list of warehouse transactions.

I want to know how many items arrived, how many were shipped (and this is trivial) but also how many are "In Order" at a particular time (and this is the difficult part)

So, each line can either be a receipt (it has a positive "qIn" value) or a shipment (positive qOut)

For example a very simple list of records could be:

ID  Item    TransactionDate OrderDate   qIn qOut
1   A       2019-01-30      2019-01-10  5   0
2   A       2019-02-20      2019-01-15  3   0
3   A       2019-03-12      2019-01-20  0   6
4   A       2019-03-30      2019-02-20  20  0

That means:
On TransactionDate 2019-01-30 Items A has arrived in quantity 5. The order for this had been created on 2019-01-10: so for that 20 days there was 5 quantity of Item A "ordered". However, when I watch at the end of January, I should see 0 for this transaction in the "ordered" measure because it arrived on January 30.

Instead, for the second record, at the end of January I should see that a quantity of 3 was "in order", because the actual arrival has been on 2019-02-20.

So, at the end of the line, the Excel pivot table should show a situation similar to this:

    Year    2019
    Month   January         February        March
            IN | Ord        IN | Ord        IN | Ord
Item
A           5      3         3    20        20   0

The simple measure of qIn is:

qIN := SUM(Transactions[qtaIn])

The measure of ordered quantity I have elucubrated at the moment (that does nothing!):

orderedQty :=
CALCULATE (
    SUMX ( Transactions; Transactions[qIn] );
    DATESBETWEEN (
        Transactions[TransactionDate];
        MINX ( Transactions; Transactions[OrderDate] );
        MAXX ( Transactions; Transactions[TransactionDate] )
    )
)

EDIT

The "InOrder" measure should be "additive" in the sense that it should not only take into account what has happened in the current month, but also how much of the InOrder from past months is yet to be received.

With a picture (but that would be to do...) the whole thing would be clearer, at least from a logic perspective. However, also with a picture, I can't see how to extract "direct measures" from that logic.

Instead, exploiting the measures already provided by @Olly, the problem could be reformulated as:

InOrderFromOtherMonths :=  Sum (qIn) where Order Month <> Current Month

(i.e. how many are arrived in current month that comes from orders taken in past months)

InOrder := Total sum of (ORDER measure)  -  InOrderFromOtherMonths

PS. I have created an Excel file with a little more interesting example.
In that file, using the "direct measure picture" the InOrder for January would be: ID 2 + ID 5 + ID 6 (orders yet opened at end of January). In values = 3+9+17=29

With the "indirect" measure would be:

Total sum of ORDER = 15+23+12=50
InOrderFromOtherMonths = 6+15=21
InOrder = Total sum of ORDER - InOrderFromOtherMonths = 50 - 21 = **29**

To clarify - using your logic for open orders in January, shouldn't open orders in February be 17 (20 ordered on ID4, - 3 received on ID2) ? — Olly
@Olly, the thing is quite confusing, maybe I can be wrong... However in my opinion open orders on February it's right to be only 20, because the ID2 are received on 20th February. I have restructured the data a bit, I think it's clearer now. — spiderman
So, to clarify your measures: IN = sum of qIn where TransactionDate in filtered month. ORDER = sum of qIn where OrderDate in filtered month and TransactionDate NOT in filtered month. Is that the right business logic? — Olly
@Olly, yes what you say seems right! (and it may be an helpful simplification) — spiderman

Olly Olly · Accepted Answer · 2019-04-12T10:12:57

Create a Calendar table, including a YYYY-MM field. If you don't already have a calendar table, you can automatically create one in PowerPivot: Design > Date Table > New

Create an ACTIVE relationship between Calendar[Date] and Transactions[TransactionDate]

Create an INACTIVE relationship between Calendar[Date] and Transactions[OrderDate]

Now create your measures:

Measure IN:

IN:=SUM ( Transactions[qIn] )

Measure ORDERS:

ORDERS:=
CALCULATE ( 
    SUM ( Transactions[qIn] ),
    USERELATIONSHIP ( 'Calendar'[Date], Transactions[OrderDate] )
)

Measure ORDER:

ORDER:=
IF ( 
    HASONEVALUE ( 'Calendar'[YYYY-MM] ),
    CALCULATE ( 
        [ORDERS], 
        FORMAT ( Transactions[TransactionDate], "YYYY-MM" ) <>  VALUES ( 'Calendar'[YYYY-MM] )
    )
)

And pivot to suit:

EDIT

After your question edit, I'm finding some of your labels confusing - but try creating the following measures:

Measure: Ordered

Ordered:=
CALCULATE ( 
    SUM ( Movements[qIn] ),
    USERELATIONSHIP ( 'Calendar'[Date], Movements[OrdDate] )
)

Measure: Received

Received:= SUM ( Movements[qIn] )

Measure: Outstanding

Outstanding:= 
VAR EOMaxDate = 
    EOMONTH ( LASTDATE ( 'Calendar'[Date] ), 0 )
RETURN
    IF ( 
        ISBLANK ( [Ordered] ) && ISBLANK ( [Received] ),
        BLANK(),
        CALCULATE ( 
            [Ordered] - [Received],
            FILTER ( 
                ALL ( 'Calendar'), 
                'Calendar'[Date] <= EOMaxDate
            )
        )   
    )

Now use those three measures in your pivot:

Or, more clearly:

See https://excel.solutions/so_55596609-2/ for example XLSX file

Calculate measure involving dates on a factTable with DAX

1 Answers

EDIT