Здравствуйте! Прошу подсказать корректные способы фильтрации диапазона по дате.
У меня есть 2 таблицы:
Данные: Дата, Кол-во (выпущенных кирпичей)
Календарь: Дата
Написаны следующие меры:
Код
1. Сумма за 5 дней. FILTER и TODAY =
var DateWithTODAY = TODAY() // находим текущую дату
return
SUMX(
FILTER(
'Данные',
'Данные'[дата] <= DateWithTODAY && 'Данные'[дата] >= DateWithTODAY -4
),
'Данные'[сумма]
)
Эта мера выдаёт корректный результат. Вопрос: каким образом можно в FILTER указывать таблицу "Календарь", а суммировать по 'Данные'[сумма]? RELATED во втором аргументе SUMX?
Код
2. Сумма за 5 месяцев. FILTER и TODAY =
var DateWithCalculation = DATE(2020, 07, 15) // предположим, что это вычисленная дата, результат какой-то меры.
return
SUMX(
FILTER(
'Данные',
'Данные'[дата] <= EDATE(DateWithCalculation, 0) && 'Данные'[дата] >= EDATE(DateWithCalculation, -4)
// необходимо захватывать весь месяц, т.е. весь июль и -4 месяца
),
'Данные'[сумма]
)
Эта мера выдаёт НЕкорректный результат. Вопрос: каким образом корректно передать в FILTER весь месяц а не 15.07.2020?
Код
3. Сумма за 5 дней. СALCULATE и DATESINPERIOD =
var DateWithTODAY = TODAY() // находим текущую дату
return
CALCULATE(
SUM('Данные'[сумма]),
DATESINPERIOD(
'Данные'[дата],
DateWithTODAY,
-4,
DAY
)
)
Эта мера не работает. Вопрос: как устранить ошибку (см. визуал в файле-примере) и вычислять корректно?
1. Сумма за 5 дней. FILTER и TODAY =
CALCULATE(
SUM( 'Данные'[сумма] ),
KEEPFILTERS( DATESBETWEEN( 'Календарь'[Date], TODAY() - 4, TODAY() ) )
)
также коррелирует с вопросом по третьей мере -
Цитата
KEEPFILTERS( DATESINPERIOD( 'Календарь'[Date], DateWithTODAY, -5, DAY ) )
но если прямо необходимо использовать конструкцию SUMX+FILTER (не знаю, зачем), то вот так работает (но на нормальном объеме данных будет медленнее предыдущей
Код
1. Сумма за 5 дней. FILTER и TODAY =
SUMX(
FILTER(
VALUES( 'Календарь'[Date] ),
'Календарь'[Date]
>= TODAY() - 4
&& 'Календарь'[Date] <= TODAY()
),
CALCULATE( SUM( 'Данные'[сумма] ) )
)
Максим Зеленский, DrillPipe, подскажите пожалуйста, как быть, если в качества параметра даты используется не TODAY(), а вычисление другой меры? У меня вычисления сбиваются.
В мере "+ 1. Сумма за 5 дней. FILTER и MeasureDate" использовал её как источник даты:
Код
+ 1. Сумма за 5 дней. FILTER и MeasureDate =
var DateWithTODAY = [DateParameter]
return
CALCULATE(
SUM( 'Данные'[сумма] ),
KEEPFILTERS( DATESBETWEEN( 'Календарь'[Date], DateWithTODAY - 4, DateWithTODAY ) )
)
В итоге вся таблица слетает. Как преобразовать "DateParameter" внутри var, чтобы остальная часть меры работала корректно?
Я даже вот так сделал:
Код
+ 1. Сумма за 5 дней. FILTER и MeasureDate =
var DateFromMeasure = [DateParameter]
var Day1 = DAY(DateFromMeasure)
var Mont1 = MONTH(DateFromMeasure)
var Year1 = YEAR(DateFromMeasure)
var DateConstruction = DATE(Year1, Mont1, Day1)
return
CALCULATE(
SUM( 'Данные'[сумма] ),
KEEPFILTERS( DATESBETWEEN( 'Календарь'[Date], DateConstruction - 4, DateConstruction ) )
)
но не работает. Не пойму в чём дело. TODAY (), мера DateParameter, и DATE() выдают даты в одинаковом формате, однако, именно дата из меры DateParameter ломает всё вычисление.
DateParameter - я использую в других мерах, где нет диапазонов дат и применяется фильтрация через FILTER - всё работает корректно. Поэтому саму меру вычисляющую дату менять не хочется.
Потому что если вы остановитесь и подумаете, как считается ваша мера [Date Parameter], то получится вот так:
Берем дату из Календаря, например, 01.01.2018. Она фильтрует строки в Данных по этой дате.
Из получившихся строк выбираем те, где год =2020 и месяц = 3, то есть ни одной строки.
Из выбранных строк берем максимальную дату, то есть BLANK().
Затем вы передаете этот BLANK в DATESBETWEEN, который, ведет себя так: If EndDate is a blank date value, then EndDate will be the latest value in the dates column, то есть ограничение по дате "сверху" не работает.
Так будет для всех дат, кроме дат за март 2020. Для дат за март 2020 года вы будете получать всегда одну и ту же дату = дате из календаря. Можете проверить.
Итого, вам нужно, чтобы [Date Parameter] не учитывал дату календаря, стоящую в строке (но учитывал возможные срезы по дате?):
Код
+ 1. Сумма за 5 дней. FILTER и MeasureDate =
VAR DateWithTODAY =
CALCULATE ( [DateParameter], ALLSELECTED ( 'Календарь' ) )
RETURN
CALCULATE (
SUM ( 'Данные'[сумма] ),
KEEPFILTERS (
DATESBETWEEN ( 'Календарь'[Date], DateWithTODAY - 4, DateWithTODAY )
)
)
Максим Зеленский, DrillPipe, подскажите пожалуйста, как решить аналогичную фильтрацию по Кварталу (найти квартал рассчитанной даты и установить диапазон -5 кварталов назад)?
Аналога EOMONTH для квартала нет. Я использовал
Код
STARTOFQUARTER({DateWithTODAY})
и
Код
ENDOFQUARTER({DateWithTODAY})
но они не работают. Просто указать номер квартала и выполнить фильтрацию по 'Данные'[Квартал] - не самая лучшая идея, т.к. необходимо учитывать ещё и год, который не фиксированный, а плавающий.
VAR DateWithTODAY =
CALCULATE ( [DateParameter], ALLSELECTED ( 'Календарь' ) )
VAR DaysInMonth =
IF ( MONTH ( DateWithTODAY ) IN { 1, 2, 3, 10, 11, 12 }, 31, 30 ) // количество дней в последнем месяце квартала
VAR EndQuarter_ForDateTODAY =
DATE ( YEAR ( DateWithTODAY ), FORMAT ( DateWithTODAY, "q" ) * 3, DaysInMonth ) // последний месяц квартала
RETURN
CALCULATE (
SUM ( 'Данные'[сумма] ),
KEEPFILTERS (
DATESINPERIOD ( 'Календарь'[Date], EndQuarter_ForDateTODAY, -5, QUARTER ) // это будет текущий квартал + 4 предыдущих. Если нужно 5 предыдущих - поменять на -6
)
)
как вариант, чуть нагляднее. возможно, вот так понадобится, если не будете использовать встроенную автоиерархию дат (хотя уже не соображаю что-то к вечеру, вроде и так норм...)
Максим Зеленский, DrillPipe, выше вы подсказали как выполнять вычисления если мы знаем крайнюю дату и делаем от неё отступ в прошлое.
Прошу подсказать решение для условия, где диапазон задаётся фильтром, при этом, мера должна выдавать результат за 3 года. Т.е. в графике сравнивается 3 года по одинаковому диапазону.
Выбираем диапазон:
Получаем результат:
Это уже готово! В модели данных я создал таблицу, которая содержит в себе дату и МесяцДень по типу: 11, 12, 31 Эту таблицу я поместил в фильтр.
Необходимо прописать ограничение на 3 года. Не получается.
Код
ОграничитьПоГоду = //необходимо, чтобы мера показывала -3 года
var MaxDate = MAX('КалендарьФильтр'[Date]) // находим максимальную дату
var MaxYear = YEAR(MaxDate) // находим максимальный год
var MinYear = MaxYear -3 // данные раньше этого года не показываем
return
CALCULATE(
SUM('Данные'[сумма])
)
по-хорошему, это уже в новую тему. Универсальное решение:
Код
ОграничитьПоГоду = //необходимо, чтобы мера показывала -3 года
var MaxDate = MAX('КалендарьФильтр'[Date]) // находим максимальную дату
var MaxYear = YEAR(MaxDate) // находим максимальный год
var MinYear = MaxYear -3 // данные раньше этого года не показываем
return
CALCULATE(
SUM('Данные'[сумма]),
FILTER(ALL('КалендарьФильтр'), 'КалендарьФильтр'[Year]>=MinYear && 'КалендарьФильтр'[Year]<=MaxYear)
)
Очень просто - не знаете, какую из функций Time Intelligence применить - используйте FILTER
ОграничитьПоГоду = //необходимо, чтобы мера показывала -3 года
var MaxDate = MAX('КалендарьФильтр'[Date]) // находим максимальную дату
var MaxYear = YEAR(MaxDate) // находим максимальный год
var MinYear = MaxYear -3 // данные раньше этого года не показываем
return
SUMX(
FILTER(
'Данные',
'Данные'[Год] >= MinYear && 'Данные'[Год] <= MaxYear
),
'Данные'[сумма]
)
Функционирует.
Вашу меру не получается приземлить в мой файл. Выдаёт только 2019 г.:
посмотрел в файл во-первых, сделайте связь календарь-данные односторонней от календаря к данным мера:
Код
ОграничитьПоГоду = //необходимо, чтобы мера показывала -3 года
var MaxDate = MAX('КалендарьФильтр'[Date]) // находим максимальную дату
var MaxYear = YEAR(MaxDate) // находим максимальный год
var MinYear = MaxYear -3 // данные раньше этого года не показываем
var result =
CALCULATE(
SUM('Данные'[сумма]),
KEEPFILTERS(FILTER(ALL('Календарь'),'Календарь'[Год]<=MaxYear && 'Календарь'[Год]>MinYear))
)
return
result
Помогите, пожалуйста, с написанием меры при похожей ситуации.
У каждого сотрудника имеется своя доля в проекте, со временем доля может изменятся. Мне необходимо, чтобы при подсчете суммы отгрузок бралась доля участия сотрудника в проекте, действовавшая на момент отгрузки.
Есть таблица с продажами (в т.ч.) по датам, группирую их в столбцом год (2022 и 2021). В каждом году одинаковый период (аналогичный для сравнений).
Нужно вывести меры продаж за крайние 7 дней и за крайние 30 дней относительно крайней даты по году в таблице.
Делаю это в Эксель, хорошо бы найти решение без переменных.
Ниже мой код. Но он не отрабатывает для правой части сводной (для 2021 года), для левой (2022) отлично работает, где скидывает весь контекст фильтров при группировки строк, тем самым определяет для всех одну общую дату (для 2022) для получения продаж за 7 и за 30 дней от неё.
(за 30 дней мера отдельная, той же конструкции)
Почему не работает для 2021 года?
Прикрепил скрины модели данных. Буду благодарен за любую подсказку.