Блог

У меня такая проблема: дана таблица фактов «Перемещения», которая содержит список складских транзакций.

Я хочу знать, сколько товаров прибыло, сколько было отправлено (и это тривиально), но также сколько «В порядке» в определенное время (и это сложная часть)

Таким образом, каждая строка может быть либо квитанцией (она имеет положительное значение «qIn»), либо отправкой (положительный qOut)

Например, очень простой список записей может быть:

 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
  

Это означает:
На дату транзакции 2019-01-30 товаров A поступило в количестве 5.
Заказ на это был создан в 2019-01-10: таким образом, за эти 20 дней было «заказано» 5 единиц товара A.
Однако, когда я просматриваю в конце января, я должен увидеть 0 для этой транзакции в «упорядоченном» показателе, потому что он поступил 30 января.

Вместо этого, для второй записи, в конце января я должен увидеть, что количество в 3 было «в порядке», потому что фактическое поступление было в 2019-02-20.

Итак, в конце строки сводная таблица Excel должна показать ситуацию, аналогичную этой:

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

Простой показатель qIn равен:

 qIN := SUM(Transactions[qtaIn])
  

Показатель заказанного количества, который я выяснил на данный момент (это ничего не дает!):

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

Редактировать

Показатель «Порядка» должен быть «аддитивным» в том смысле, что он должен учитывать не только то, что произошло в текущем месяце, но также и то, какую часть порядка за прошлые месяцы еще предстоит получить.

С изображением (но это было бы достаточно …) все было бы понятнее, по крайней мере, с точки зрения логики. Однако, также с изображением, я не вижу, как извлечь «прямые меры» из этой логики.

Вместо этого, используя меры, уже предоставленные @Olly, проблему можно переформулировать как:

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

(т. е. сколько поступило в текущем месяце из заказов, принятых в прошлые месяцы)

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

PS.
Я создал файл Excel с немного более интересным примером.

В этом файле, используя «изображение прямого измерения», InOrder для января будет:
ID 2 ID 5 ID 6 (заказы еще не открыты в конце января).
В значениях = 3 9 17 =29

С помощью «косвенной» меры было бы:

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

Создайте таблицу календаря, включающую YYYY-MM поле. Если у вас еще нет таблицы календаря, вы можете автоматически создать ее в PowerPivot: Design > Date Table > New

Создайте АКТИВНУЮ связь между Calendar[Date] и Transactions[TransactionDate]

Создайте НЕАКТИВНУЮ связь между Calendar[Date] и Transactions[OrderDate]

Теперь создайте свои показатели:

Измерение В:

 IN:=SUM ( Transactions[qIn] )
  

Заказы на измерение:

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

ПОРЯДОК измерения:

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

И сверните в соответствии:

Редактировать

После редактирования вашего вопроса я нахожу некоторые из ваших меток запутанными, но попробуйте создать следующие показатели:

Показатель: Упорядоченный

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

Показатель: Получен

 Received:= SUM ( Movements[qIn] )
  

Показатель: Невыполненный

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

Теперь используйте эти три показателя в сводной таблице:

Или, более четко:

Смотрите https://excel.solutions/so_55596609-2 / например, файл XLSX