Страницы: 1
RSS
Расчет ежедневных остатков при наличии данных по остаткам на промежуточные даты
 
Уважаемые форумчане, добрый день! Выявилась проблема с расчетом ежедневных остатков. Есть промежуточные ежемесячные данные по остаткам на 1-й день месяца (1 января, 1 февраля и т.д.) и есть оборот (приходы, расходы) по каждому дню в отдельной таблице. Необходимо рассчитать начальный и конечный остаток каждого дня.
Логику расчета конечного остатка понимаю - надо вычислить ближайшую, более раннюю дату промежуточного остатка  к минимальной дате установленного срезом временного промежутка. И затем к вычисленному на эту дату промежуточному остатку прибавить обороты за промежуток с даты этого промежуточного остатка до максимальной даты среза. Попытался создать меру по этому алгоритму.
Код
КонечныйОстаток = var MinDate = MIN('_КалендарьAUTO'[Data])
var MaxDate = MAX('_КалендарьAUTO'[Data])
var DateUst = 
    maxx(
        filter(
            'ПромежИтоги',
            'ПромежИтоги'[Дата]>=MinDate&&
            'ПромежИтоги'[Дата]<=MaxDate
        ),
        'ПромежИтоги'[Дата]
    )
return
CALCULATE(
    SUM('Оборот'[ОборотКолич]),
    FILTER(
        ALL('_КалендарьAUTO'),
        '_КалендарьAUTO'[Data]>=DateUst&&
        '_КалендарьAUTO'[Data]<=MaxDate
        )
)+
CALCULATE(
    SUM('ПромежИтоги'[ПромежКоличество]),
        FILTER(
                ALL('_КалендарьAUTO'),
                '_КалендарьAUTO'[Data] = DateUst
            )
)

Но она считает неправильно. Просьба помочь в решении вопроса.
 
Кстати, пока просматривал подобный вопрос не форуме, увидел, что Максим Зеленский написал, что "...ну и MAXX+FILTER - это же ужас-ужас с точки зрения производительности...". А у меня в коде эта конструкция присутствует.  
 
Sergey Chernichenko,
Код
КонечныйОстаток = 
VAR MaxDate =
    MAX ( '_КалендарьAUTO'[Data] )
VAR initialStockDate =
    CALCULATE (
        Max ( 'ПромежИтоги'[Дата] ),
        ALL ( '_КалендарьAUTO' ),
        '_КалендарьAUTO'[Data] <= MaxDate
    )
VAR initialStock =
    CALCULATE (
        MIN ( 'ПромежИтоги'[ПромежКоличество] ),
        ALL ( '_КалендарьAUTO' ),
        '_КалендарьAUTO'[Data] = initialStockDate
    )
VAR changeOverPeriod =
    CALCULATE (
        SUM ( 'Оборот'[ОборотКолич] ),
        ALL ( '_КалендарьAUTO' ),
        DATESBETWEEN ( '_КалендарьAUTO'[Data], initialStockDate, MaxDate )
    )
VAR result =
    IF ( [ShowValueForDates], initialStock + changeOverPeriod )
RETURN
    result

Скрытая мера (чтобы не показывать расчет в датах после последних данных):
Код
ShowValueForDates = 
VAR LastDateWithData =
    CALCULATE ( MAX ( 'Оборот'[Дата] ), REMOVEFILTERS () )
VAR LastDateWithStock =
    CALCULATE ( MAX ( 'ПромежИтоги'[Дата] ), REMOVEFILTERS () )
VAR FirstDateVisible =
    MIN ( '_КалендарьAUTO'[Data] )
VAR Result = FirstDateVisible <= LastDateWithData
    || FirstDateVisible <= LastDateWithStock
RETURN
    Result
Изменено: surkenny - 19.05.2022 23:11:40
 
surkenny, благодарю за помощь! В коде разобрался, понял в чем была ошибка моей логики. Особенно приятно, что код дополнен "невыводом" данных, лежащих за пределами временного среза. Это мелочь, но однозначно показатель работы эксперта - профессионала. Ну и из кода вы убрали MAXX+FILTER, значит Максим Зеленский не будет хмурить на меня брови))).
 
Sergey Chernichenko,
1. Если результат итогов Вам не нужен и начальный остаток всегда на 1 число, то можно вообще простым выражением посчитать:
Код
КонечныйОстаток2 =
IF (
    [ShowValueForDates],
    CALCULATE (
        SUM ( 'ПромежИтоги'[ПромежКоличество] ) + SUM ( 'Оборот'[ОборотКолич] ),
        DATESMTD ( '_КалендарьAUTO'[Data] )
    )
)

2. Может, я Вас огорчу, но выражения абсолютно эквивалентны (считайте, что первое всегда неявно преобразуется во второе):
Код
SUM ( 'Оборот'[ОборотКолич] )
SUMX ( 'Оборот', 'Оборот'[ОборотКолич] )

А мера
Код
Мера =
CALCULATE (
    SUM ( 'Оборот'[ОборотКолич] ),
    'Номенклатура'[Наименование] = "Хлеб"
)

это лишь упрощенная запись
Код
Мера =
CALCULATE (
    SUM ( 'Оборот'[ОборотКолич] ),
    FILTER ( ALL ( 'Номенклатура' ), 'Номенклатура'[Наименование] = "Хлеб" )
)

Таким образом следующие меры идентичны:
Код
Мера =
CALCULATE (
    SUM ( 'Оборот'[ОборотКолич] ),
    'Номенклатура'[Наименование] = "Хлеб"
)

Код
Мера =
CALCULATE (
    SUMX ( 'Оборот', 'Оборот'[ОборотКолич] ),
    FILTER ( ALL ( 'Номенклатура' ), 'Номенклатура'[Наименование] = "Хлеб" )
)
Изменено: surkenny - 20.05.2022 08:51:02
 
surkenny, спасибо за дополнительную информацию! В коде сообщения #3 при тестировании выявил неточность - если смотреть по одной номенклатурной позиции, считает правильно, если несколько - нет. Для себя выявил, что MIN необходимо заменить на SUM, т.е.
Код
VAR initialStock =    CALCULATE (
        MIN ( 'ПромежИтоги'[ПромежКоличество] ),
        ALL ( '_КалендарьAUTO' ),
        '_КалендарьAUTO'[Data] = initialStockDate
поменять на
Код
VAR initialStock =    CALCULATE (
        SUM ( 'ПромежИтоги'[ПромежКоличество] ),
        ALL ( '_КалендарьAUTO' ),
        '_КалендарьAUTO'[Data] = initialStockDate

Вроде бы и по логике правильно.

По вопросам - результат итогов не нужен (меру использую для формирования графика остатков по дням - отдельных позиций и номенклатурных групп), Данные по промежуточному остатку на первое число каждого месяца присутствуют, но всегда держу в уме - а вдруг по какой-то позиции данных не будет (пропущен месяц). Правильно ли при этом посчитает мера.

И, если будет возможность ответить, задам ещё один вопрос. Мера сейчас рассчитывает остаток на каждый день. А как получить сумму этих значений по каждому дню (чтобы в дальнейшем использовать данные для расчета оборачиваемости)? Какой-то цикл надо организовывать?

 
Цитата
Sergey Chernichenko написал:
Правильно ли при этом посчитает мера.
Мера с накопительным итогом за месяц - нет. Необходимо, чтобы данные были на начало каждого месяца.
Моей первой мере все равно. Там ищется начальный остаток, ближайший к последней дате в текущем контексте фильтра, и к нему прибавляется изменение за период от даты остатка до максимальной в текущем контексте фильтра.
Цитата
Sergey Chernichenko написал:
Вроде бы и по логике правильно.
Да, мой косяк. SUM необходимо использовать.
Цитата
Sergey Chernichenko написал:
А как получить сумму этих значений по каждому дню (чтобы в дальнейшем использовать данные для расчета оборачиваемости)?
Сумма
Код
КонОстСум = 
IF ( [ShowValueForDates], SUMX ( '_КалендарьAUTO', [КонечныйОстаток] ) )

Среднее
Код
КонОстСр = 
IF ( [ShowValueForDates], DIVIDE ( SUMX ( '_КалендарьAUTO', [КонечныйОстаток] ), COUNTROWS ( '_КалендарьAUTO' ) ) )
Изменено: surkenny - 20.05.2022 13:03:21
 
surkenny, спасибо! Ранее я пытался создать, подобную конструкцию, но не с SUMX, а с SUM. У меня ничего не получилось и я сделал для себя вывод, что этот вариант не работает и всё нужно загонять в меру. Оказывается, я ошибался с оператором и, на самом деле, всё считает. Если SUMX считает, значит, по идее, и CALCULATE может считать скользящую оборачиваемость на каждую дату (отношение продаж за промежуток с текущей даты до даты минус 30 дней к среднему остатку за этот же промежуток времени). Хотя, возможно, не CALCULATE, а CALCULATETABLE, судя по обсуждаемому коду.
 
Цитата
Sergey Chernichenko написал:
Если SUMX считает, значит, по идее, и CALCULATE может считать
Вашу мысль не понял. Особенно цитируемую часть. CALCULATE ничего не "считает", она модифицирует контекст фильтра. Просто SUM "посчитает" сумму в текущем контексте фильтра. А с помощью CALCULATE(SUM,...) можно этот контекст изменить.
 
surkenny, прошу прощения за задержку с ответом. Да, согласен, моя цитируемая вами фраза некорректна. Попытался на реальной модели посчитать ежедневную оборачиваемость каждой номенклатуры (соотношение продаж за предыдущий месяц к среднему остатку), рассчитанному по вышеуказанной методологии, но 64 Гб оперативной памяти не хватило для данного действия. Хотя, если таблица остатков на каждый день не расчетная из двух таблиц, как у меня в примере,  а загружаемая единая, он с ней справляется. Наверное, очень большой объём информации приходится держать в оперативке. Одну номенклатурную позицию считает быстро и нормально, при 300 позиций говорит, что не хватает памяти. Буду думать. В любом случае, ваша помощь в расчете конечных остатков и открытие по обработке мер через SUMX были очень ценны для меня, за что я вам крайне благодарен.
Страницы: 1
Наверх