Страницы: 1
RSS
DAX - сравнение списка из столбца со значением строки
 
Добрый день!

Прошу помочь в написании формулы для для сравнения списка значений из столбца со значение в строке.


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

Если тип продажи "Подключение", то мы смотрим на дату окончания всех поставок с типом "Продление" и если дата оплаты счета меньше даты окончания поставки на 90 дней (, либо больше даты окончания поставки на 30 дней, то такая сделка определяется как "Допродажа". Если данное условие не выполняется, то сделку определяем как "Продажа".

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


На уровне работы с формулой в самом Excel вопрос решался вот такой формулой:
=ЕСЛИ([@[Тип предложения]]="Продление";"Продление";ЕСЛИ(СЧЁТЕСЛИМН([ИНН];[@ИНН];[Тип предложения];"Продление";[Дата окончания поставки];">="&[@[Дата оплаты счета]]-30;[Дата окончания поставки];"<="&[@[Дата оплаты счета]]+90)>0;"Допродажа";ЕСЛИ(СЧЁТЕСЛИМН([ИНН];[@ИНН];[Тип предложения];"Продление")=0;"Новая продажа";[@[Тип предложения]])))

Работали бы в таком формате и дальше, но количество строк более 160 000 и это вызывает серьезные временные проблемы при обновлении данных.


Прошу помощи в данном вопросе
 
Владимир Шелестов, вообще логики не понял :) Сложность только понять Вашу мысль.
Мы зачем определяем наименьшую и наибольшую дату у подключения?
Ваша формула начинается с =ЕСЛИ([@[Тип предложения]]="Продление";"Продление";... То есть, если Тип продление, то его и оставляем. Тогда какой тип нужно "проверить"?
Почему 8 и 9 строки попадают, а 10 и 11 нет можно прямо словами пояснить? :) С диапазоном между мин и макс датами в примере они никак не пересекаются.
Пробы понять Вашу логику:
Код
=
VAR connectionDate =
    CALCULATE (
        MAX ( 'Таблица1'[Дата оплаты счета] );
        ALLEXCEPT ( 'Таблица1'; 'Таблица1'[ИНН] );
        'Таблица1'[Тип продажи] = "Подключение"
    )
VAR needChekType =
    'Таблица1'[Тип продажи] = "Продление"
        && NOT ISBLANK ( 'Таблица1'[Дата окончания поставки] )
VAR curPayDate = 'Таблица1'[Дата оплаты счета]
VAR filterTable =
    FILTER (
        ALLEXCEPT ( 'Таблица1'; 'Таблица1'[ИНН] );
        'Таблица1'[Тип продажи] = "Продление"
            && 'Таблица1'[Дата окончания поставки] >= curPayDate - 30
            && 'Таблица1'[Дата окончания поставки] <= curPayDate + 90
    )
VAR noMatch =
    ISEMPTY ( filterTable )
VAR result =
    IF (
        needChekType;
        IF ( noMatch; "Новая продажа"; 'Таблица1'[Тип продажи] );
        'Таблица1'[Тип продажи]
    )
RETURN
    result
Изменено: surkenny - 18.05.2022 13:44:30
 
В дополнение.
1. Это условие фильтра таблицы 'Таблица1'[Тип продажи] = "Продление" меня смущает (это из Вашего описания и формулы). Попахивает рекурсией. Мы ведь должны проверить корректность типа "Продление". Но проверяем его по наличию строк с типом "Продление":
Проверили мы какую-то строку (СТРОКА_1), тип оставляем "Продление" на основании единственной другой строки (СТРОКА_2). с типом "Продление", соответствующей по датам. А потом проверили СТРОКУ_2 и оказалось, что нужно поменять тип на "Новая продажа". Теперь и СТРОКА_1 не соответствует типу "Продление".
2. Ваша формула выдает ожидаемы результат:
=ЕСЛИ([@[Тип продажи]]="Продление";"Продление";ЕСЛИ(СЧЁТЕСЛИМН([ИНН];[@ИНН];[Тип продажи];"Продление";[Дата окончания поставки];">="&[@[Дата оплаты счета]]-30;[Дата окончания поставки];"<="&[@[Дата оплаты счета]]+90)>0;"Допродажа";ЕСЛИ(СЧЁТЕСЛИМН([ИНН];[@ИНН];[Тип продажи];"Продление")=0;"Новая продажа";[@[Тип продажи]])))
Изменено: surkenny - 18.05.2022 13:57:40
 
surkenny, накосячил с датами в примере

Если тип продажи "Продление", то type будет "Продление", эти сделки уточнения не требуют

В данном примере уточнение требуется только по 7 строке, где сделка с типом продажи "Подключение". Именно для даты оплаты в этой строке 11.01.2022 мы определяем период, внутри которого должна находится дата окончания поставки. Т.е. для дата оплаты 11.01.2022 допустимый период с 12.12.2021 по 11.04.2022

По уточненному рисунку:
Строки 8-11 и 14-15 не учитываются т.к. дата окончания поставки < даты оплаты счета с типом продажи "Подключение" 11.01.2022 - 30 дней (12.12.2021)
Строки 12-13 и 16-19 учитываются, т.к. дата окончания поставки >= даты оплаты счета с типом продажи "Подключение" 11.01.2022 - 30 дней (12.12.2021) и <= даты оплаты счета с типом продажи "Подключение" 11.01.2022 + 90 дней (11.04.2022)


Т.к. у нас учитывается хотя бы одна строка, то данную сделку нужно определить как "Допродажа"
Если бы сделка не учитывалась ни одной строкой с не пустой датой окончания поставки, то она определилась бы как "Продажа"


По формуле с ЕСЛИ:
Указано измерение "Тип предложения" по которому считали раньше. Сейчас переезжаем на измерение "Тип продажи"

Цитата
2. Ваша формула выдает ожидаемы результат:
=ЕСЛИ([@[Тип продажи]]="Продление";"Продление";ЕСЛИ(СЧЁТЕСЛИМН([ИНН];[@ИНН];[Тип продажи];"Продление";[Дата окончания поставки];">="&[@[Дата оплаты счета]]-30;[Дата окончания поставки];"<="&[@[Дата оплаты счета]]+90)>0;"Допродажа";ЕСЛИ(СЧЁТЕСЛИМН([ИНН];[@ИНН];[Тип продажи];"Продление")=0;"Новая продажа";[@[Тип продажи]])))
Вот такой результат и нужно получить в Pivot :)
Изменено: Владимир Шелестов - 18.05.2022 14:30:33
 
Владимир Шелестов,
Код
type =
VAR needChekType = 'Таблица1'[Тип продажи] <> "Продление"
VAR curINN = 'Таблица1'[ИНН]
VAR curPayDate = 'Таблица1'[Дата оплаты счета]
VAR filterTable =
    FILTER (
        ALL ( 'Таблица1' );
        'Таблица1'[ИНН] = curINN
            && 'Таблица1'[Тип продажи] = "Продление"
            && 'Таблица1'[Дата окончания поставки] >= curPayDate - 30
            && 'Таблица1'[Дата окончания поставки] <= curPayDate + 90
    )
VAR noMatch =
    ISEMPTY ( filterTable )
VAR result =
    IF (
        needChekType;
        IF ( noMatch; "Новая продажа"; "Допродажа" );
        'Таблица1'[Тип продажи]
    )
RETURN
    result
Изменено: surkenny - 18.05.2022 14:44:05
 
surkenny, мagic :)

Спасибо огромнейшее!
Страницы: 1
Наверх