Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
RSS
DAX. Суммирование значений за определённый диапазон дат, Необходимо отфильтровать диапазон дат и суммировать значения
 
Здравствуйте!
Прошу подсказать корректные способы фильтрации диапазона по дате.

У меня есть 2 таблицы:
  1. Данные: Дата, Кол-во (выпущенных кирпичей)
  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
    )
)
Эта мера не работает.
Вопрос: как устранить ошибку (см. визуал в файле-примере) и вычислять корректно?
Изменено: ivanka - 10 авг 2020 09:34:53 (Заменил файл примера. Скорректировал меру № 3.)
 
так будет сильно быстрее:
Код
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( 'Данные'[сумма] ) )
)
по второй мере: неправильно выбрали функцию:
Цитата
'Данные'[дата] <= EOMONTH(DateWithCalculation, 0) && 'Данные'[дата] >= EOMONTH(DateWithCalculation, -5) + 1
F1 творит чудеса
 
Максим Зеленский, благодарю! Буду тестировать!

Цитата
Максим Зеленский написал:
но если прямо необходимо использовать конструкцию SUMX+FILTER
необходимости нет, просто я написал меру наиболее понятным для себя способом. Ваш вариант для меня более интересен.
 
Код
CALCULATE(
            SUM( 'Данные'[сумма] ),
            FILTER ( Dates,  Dates[Date] IN DATESINPERIOD ( Dates[Date], TODAY(), -4, DAY ) )
)
 
DrillPipe, здесь FILTER уже не нужен.
F1 творит чудеса
 
Максим Зеленский, DrillPipe, подскажите пожалуйста, как быть, если в качества параметра даты используется не TODAY(), а вычисление другой меры? У меня вычисления сбиваются.

Я вычислил дату:
Код
DateParameter = 
MAXX(
    FILTER(
        'Данные',
        'Данные'[Месяц] = 3 && 'Данные'[Год] = 2020),
    'Данные'[дата])


В мере "+ 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 - всё работает корректно. Поэтому саму меру вычисляющую дату менять не хочется.

Благодарю!  
Изменено: ivanka - 11 авг 2020 11:18:08
 
Потому что если вы остановитесь и подумаете, как считается ваша мера [Date Parameter], то получится вот так:
  1. Берем дату из Календаря, например, 01.01.2018. Она фильтрует строки в Данных по этой дате.
  2. Из получившихся строк выбираем те, где год =2020 и месяц = 3, то есть ни одной строки.
  3. Из выбранных строк берем максимальную дату, то есть BLANK().
  4. Затем вы передаете этот 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 )
        )
    )
Ну или ALL вместо ALLSELECTED
F1 творит чудеса
 
ну и MAXX+FILTER - это же ужас-ужас с точки зрения производительности.
Код
=
CALCULATE ( MAX ( 'Данные'[дата] ), 'Данные'[Месяц] = 3, 'Данные'[Год] = 2020 )
F1 творит чудеса
 
Максим Зеленский, благодарю Вас!
Как-то странно получилось с мерой....

Код
DateParameter = 
MAXX(
    FILTER(
        'Данные',
        'Данные'[Месяц] = 3 && 'Данные'[Год] = 2020),
    'Данные'[дата])

Я не учёл контекст фильтров.... совсем забыл!
Благодарю за помощь!  
Изменено: ivanka - 12 авг 2020 08:46:50
 
Цитата
ivanka написал:
Я не учёл контекст фильтров
Тут скорее всего контекст строк.
 
Максим Зеленский, DrillPipe,
подскажите пожалуйста, как решить аналогичную фильтрацию по Кварталу (найти квартал рассчитанной даты и установить диапазон -5 кварталов назад)?

Аналога EOMONTH для квартала нет.
Я использовал
Код
STARTOFQUARTER({DateWithTODAY})
и
Код
ENDOFQUARTER({DateWithTODAY})
но они не работают.
Просто указать номер квартала и выполнить фильтрацию по 'Данные'[Квартал] - не самая лучшая идея, т.к. необходимо учитывать ещё и год, который не фиксированный, а плавающий.

Код
+ 3. Сумма за 5 кварталов = 
VAR DateWithTODAY =
    CALCULATE ( [DateParameter], ALLSELECTED ( 'Календарь' ) )
VAR StartQuarter =
    STARTOFQUARTER({DateWithTODAY})

VAR EndQuarter =
    ENDOFQUARTER({DateWithTODAY})

return
CALCULATE(
    SUM( 'Данные'[сумма] ),
    KEEPFILTERS(
        DATESBETWEEN(
            'Календарь'[Date],
            StartQuarter - 4,
            EndQuarter 
        )
    )
)


Благодарю!  
Изменено: ivanka - 12 авг 2020 14:39:20
 
ivanka,
Попробуйте так
Код
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 
        )
    )
Capture.PNG (78.41 КБ)
Изменено: DrillPipe - 12 авг 2020 18:55:43
 
DrillPipe, Благодарю за шикарное решение!
Microsoft не придумала, а вы придумали!)
Было бы намного проще, если в добавок к EOMONTH была бы EOQUARTER!
Изменено: ivanka - 12 авг 2020 18:23:34
 
ivanka,
Думаю, что Максим найдет более простое решение
 
Код
+ 3. Сумма за 5 кварталов =
VAR DateWithTODAY =
    CALCULATE ( [DateParameter], ALLSELECTED ( 'Календарь' ) )
VAR EndQuarter =
    CALCULATE (
        ENDOFQUARTER ( 'Календарь'[Date] ),
        'Календарь'[Date] = DateWithTODAY
    )
RETURN
    CALCULATE (
        SUM ( 'Данные'[сумма] ),
        KEEPFILTERS ( DATESINPERIOD ( 'Календарь'[Date], EndQuarter, -5, QUARTER ) )
    )
как вариант, чуть нагляднее. возможно, вот так понадобится, если не будете использовать встроенную автоиерархию дат (хотя уже не соображаю что-то к вечеру, вроде и так норм...)
Код
+ 3. Сумма за 5 кварталов =
VAR DateWithTODAY =
    CALCULATE ( [DateParameter], ALLSELECTED ( 'Календарь' ) )
VAR EndQuarter =
    CALCULATE (
        ENDOFQUARTER ( 'Календарь'[Date] ),
        'Календарь'[Date] = DateWithTODAY,
        ALL ( 'Календарь' )
    )
RETURN
    CALCULATE (
        SUM ( 'Данные'[сумма] ),
        KEEPFILTERS ( DATESINPERIOD ( 'Календарь'[Date], EndQuarter, -5, QUARTER ) )
    )
F1 творит чудеса
 
Максим Зеленский,
Делал как у вас сначала в DAX Studio, что-то криво выдавал набор дат.
А в PBI не проверил - вот и смастерил костыль :(
Изменено: DrillPipe - 12 авг 2020 19:07:36
 
еще вариант конца квартала:
Код
VAR EndQuarter =
EOMONTH(
    DateWithTODAY,
    QUARTER( DateWithTODAY ) * 3
        - MONTH( DateWithTODAY )
)
F1 творит чудеса
 
О, функцию квартал добавили? А я все по старинке через формат добываю:)
 
Максим Зеленский, DrillPipe, господа, вы шикарно разобрали задачу! Эти решения уже помогают мне и принесут пользу другим людям. Благодарю вас!
 
Максим Зеленский, DrillPipe, выше вы подсказали как выполнять вычисления если мы знаем крайнюю дату и делаем от неё отступ в прошлое.

Прошу подсказать решение для условия, где диапазон задаётся фильтром, при этом, мера должна выдавать результат за 3 года.
Т.е. в графике сравнивается 3 года по одинаковому диапазону.

Выбираем диапазон:



Получаем результат:



Это уже готово!
В модели данных я создал таблицу, которая содержит в себе дату и МесяцДень по типу: 11, 12, 31
Эту таблицу я поместил в фильтр.

Необходимо прописать ограничение на 3 года. Не получается.

Код
ОграничитьПоГоду = //необходимо, чтобы мера показывала -3 года
var MaxDate = MAX('КалендарьФильтр'[Date]) // находим максимальную дату
var MaxYear = YEAR(MaxDate) // находим максимальный год
var MinYear = MaxYear -3 // данные раньше этого года не показываем

return 
CALCULATE(
    SUM('Данные'[сумма])
)


Благодарю!
Изменено: ivanka - 24 авг 2020 15:10:23
 
по-хорошему, это уже в новую тему.
Универсальное решение:
Код
ОграничитьПоГоду = //необходимо, чтобы мера показывала -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
F1 творит чудеса
 
Максим Зеленский, благодарю!
Я только что использовал:
Код
ОграничитьПоГоду = //необходимо, чтобы мера показывала -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

и перестаньте делать SUMX по FILTER, ну зачем?
Цитата
ivanka написал:
SUMX(    FILTER(        'Данные',        'Данные'[Год] >= MinYear && 'Данные'[Год] <= MaxYear ), 'Данные'[сумма])
F1 творит чудеса
 
Максим Зеленский, благодарю! Всё отлично работает!
Страницы: 1
Читают тему (гостей: 1)
Наверх