Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Расчет продаж за предыдущий период (DAX), DAX
 
#1
16.08.2022 11:31:04
Коллеги, нужна ваша помощь:

Есть модель (см вложение)


Все данные - это таблица с данными о продажах за все периоды (текущий и прошлый)

Календарь - справочник с непрерывными датами

Календарь задействован в фильтре по дате.

В данном фильтре выбирается произвольная дата.

Нужно посчитать продажи (сумма по КолПроданоРозн) за прошлый период (диапазон дат от начала аналогичного месяца в прошлом году до аналогичного дня в прошлом году).

Т.е если в фильтре выбрано 18.08.2022, то сумму нужно посчитать за 01.08.2021 - 18.08.2021

При этом ПродажаДАТАЗаписиВБД <= 18.08.2021

Т.е если в фильтре выбрано 15.08.2022, то сумму нужно посчитать за 01.08.2021 - 15.08.2021

При этом ПродажаДАТАЗаписиВБД <= 15.08.2021


У меня вот такая мера не дает должный результат. Последнее условие не отрабатывает.

Продажи за прошлый период = CALCULATE(

   SUM('Все данные'[КолПроданоРозн]),

   DATESMTD(DATEADD('Календарь'[Date], -1, YEAR)),

   'Все данные'[ПродажаДатаЗаписиВБД]<=MAX('Все данные'[ПродажаДата])

)

Подскажите, пожалуйста, в чем ошибка?
 
 
 
#2
16.08.2022 13:49:51
Код
=
CALCULATE (
    SUM ( 'Таблица'[сумма] );
    FILTER (
        ALL ( 'Календарь' );
        'Календарь'[Дата]
            <= IF (
                EOMONTH (
                    DATE ( YEAR ( MAX ( 'Календарь'[Дата] ) ) - 1; MONTH ( MAX ( 'Календарь'[Дата] ) ); 1 );
                    0
                )
                    < DATE ( YEAR ( MAX ( 'Календарь'[Дата] ) ) - 1; MONTH ( MAX ( 'Календарь'[Дата] ) ); CALCULATE (
                        COUNTROWS ( 'Календарь' );
                        FILTER (
                            ALL ( 'Календарь' );
                            'Календарь'[Дата]
                                >= DATE ( YEAR ( MAX ( 'Календарь'[Дата] ) ); MONTH ( MAX ( 'Календарь'[Дата] ) ); 1 )
                                && 'Календарь'[Дата] <= MAX ( 'Календарь'[Дата] )
                        )
                    ) );
                EOMONTH (
                    DATE ( YEAR ( MAX ( 'Календарь'[Дата] ) ) - 1; MONTH ( MAX ( 'Календарь'[Дата] ) ); 1 );
                    0
                );
                DATE ( YEAR ( MAX ( 'Календарь'[Дата] ) ) - 1; MONTH ( MAX ( 'Календарь'[Дата] ) ); CALCULATE (
                    COUNTROWS ( 'Календарь' );
                    FILTER (
                        ALL ( 'Календарь' );
                        'Календарь'[Дата]
                            >= DATE ( YEAR ( MAX ( 'Календарь'[Дата] ) ); MONTH ( MAX ( 'Календарь'[Дата] ) ); 1 )
                            && 'Календарь'[Дата] <= MAX ( 'Календарь'[Дата] )
                    )
                ) )
            )
            && 'Календарь'[Дата]
                >= DATE ( YEAR ( MAX ( 'Календарь'[Дата] ) ) - 1; MONTH ( MAX ( 'Календарь'[Дата] ) ); 1 )
    )
)


для даты 29.02.16 будут данные за февраль 15 года и 01.03.15 так работает и для 29 февраля
Изменено: mechanix 85 - 16.08.2022 14:04:43
 
 
 
#3
16.08.2022 20:38:31
Андрей Швейников, жаль что нет примера, мне проблематично все в воображении представить и попробовать.(пример не должен содержать конфиденциальных данных, он должен отражать суть задачи)
но уже есть несколько моментов, вы пишете что
Цитата
При этом ПродажаДАТАЗаписиВБД <= 15.08.2021
т.е. из даты уже вычтен год,а в формуле
Цитата
 'Все данные'[ПродажаДатаЗаписиВБД]<=MAX('Все данные'[ПродажаДата])
во второй дате остается 22-й год
Изменено: Lari - 16.08.2022 20:39:39
 
 
 
#4
16.08.2022 22:06:52
@Lari Вы правы, MAX('Все данные'[ПродажаДата]) вернет 2022.
Можно как-то поправить мое выражение, чтобы MAX('Все данные'[ПродажаДата]) возвращала 2021?
По сути нужно изменить контекст у данной функции, но я не знаю, как это сделать, не нарушая  правил DAX.

Могу дать ссылку на файл pbx (power bi)

@mechanix 85 Спасибо! Сейчас займусь разбором Вашего решения.
 
 
 
#5
17.08.2022 00:07:32
Андрей Швейников, попробуйте так:
Код
Продажи за прошлый период = CALCULATE(
   SUM('Все данные'[КолПроданоРозн]),
   CALCULATETABLE(DATEADD(DATESMTD('Календарь'[Date]), -1, YEAR), 'Календарь'[DateWithSales] = TRUE )
)

Вычисляемый столбец в календаре:
DateWithSales = 'Календарь'[Date] <= MAX ( 'Все данные'[ПродажаДата] )
И скиньте хотя бы ссылку на pbix, если файл большой.
Изменено: surkenny - 17.08.2022 00:58:18
 
 
 
#6
17.08.2022 10:54:59
Андрей Швейников,
Цитата
Могу дать ссылку на файл pbx (power bi)
но пример нужно как-то заслужить?
(уточняю, без конфиденциальных данных)
 
 
 
#7
17.08.2022 14:35:08
@Lari, @surkenny
Сделал пример (см вложение)
Проблемы в расчете меры "Продажи за прошлый период"
Сейчас она считается так:

Продажи за прошлый период = CALCULATE(

  SUM('Все данные'[КолПроданоРозн]),

  DATESMTD(DATEADD('Календарь'[Date], -1, YEAR)),

  'Все данные'[ПродажаДатаЗаписиВБД]<=MAX('Все данные'[ПродажаДата])

)

Это неправильно.
Последнее условие, как заметил Lari, не будет работать, т.к MAX('Все данные'[ПродажаДата]) вернет максимальную дату продажи за текущий период с учетом фильтрации по календарю.
А нужно получить максимальную дату за прошлый период.
Последнее условие в мере необходимо, чтобы при расчете продаж за прошлый период исключить из выборки строчки, похожие на пример снизу (продажа была совершена 11.08.2021, но занесли ее в БД 24.08.2021, т.е при расчете продаж с 01.08.2021 по 11.08.2021 данная строчка не должна учитываться).

Изменено: Андрей Швейников - 17.08.2022 14:37:32
 
 
 
#8
17.08.2022 14:57:09
А второй аргумент фильтра заменить на EDATE(MAX('Все данные'[ПродажаДата]),-12,MONTH) поможет?
Изменено: surkenny - 17.08.2022 14:57:33
 
 
 
#9
17.08.2022 17:00:10
@surkenny Помогло! Спасибо, дружище!

Еще я обнаружил у себя логическую ошибку: если отталкиваться от MAX('Все данные'[ПродажаДата]), то можно упустить часть продаж в прошлом периоде. Например, если в календаре выбрать 05.08.2022, то продаж за текущий период в этот день может не быть, а продажи за прошлый период в этот день были. Поэтому я переписал меру вот так, вроде все сходится по цифрам:

Продажи за прошлый период = CALCULATE(
   SUM('Все данные'[КолПроданоРозн]),
   'Все данные'[ПродажаДата] <= EDATE(MAX('Календарь'[Date]), -12),
   'Все данные'[ПродажаДатаЗаписиВБД] <= EDATE(MAX('Календарь'[Date]), -12)
)

ВСЕМ ОГРОМНОЕ СПАСИБО ЗА ПОМОЩЬ!
 
 
 
#10
17.08.2022 20:40:12
Код
Продажи за прошлый период copy =
VAR _day =MAX ( 'Календарь'[Date] )
VAR _LastYear = EDATE ( _day; -12 )
VAR _FirstLY = DATE ( YEAR ( _LastYear ); MONTH ( _LastYear ); 01 )
RETURN
    CALCULATE (
        SUM ( 'Все данные'[КолПроданоРозн] );
        DATESBETWEEN ( 'Календарь'[Date]; _FirstLY; _LastYear );
        'Все данные'[ПродажаДатаЗаписиВБД] <= _LastYear
    )
С использованием переменных
 
 
 
#11
19.08.2022 08:33:08
Lari, удобный вариант! В перспективе можно варьировать обеими границами календаря. Возьму на вооружение. Спасибо!
 
 
 
Страницы: 1
Наверх