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

Есть таблица с заявками:
Название задач Начало Окончание
Задача 1 01.01.2021 03.01.2021
Задача 2 02.01.2021 04.01.2021
Задача 3 03.01.2021 05.01.2021
Задача 4 04.01.2021 06.01.2021
Задача 5 05.01.202107.01.2021
Мне нужно в PowerPivot, в мере посчитать количество выполняющихся заявок на каждый день.
Т.е. дата выполнения - это промежуток от даты начала до даты окончания.

Итоговый результат должен быть таким:
День ЗначениеПояснение
01.01.2021 1 Задача 1
02.01.2021 2 Задача 1 + Задача 2
03.01.2021 3 Задача 1 + Задача 2 + Задача 3
04.01.2021 3 Задача 2 + Задача 3 + Задача 4
05.01.2021 3 Задача 3 + Задача 4 + Задача 5
06.01.2021 2 Задача 4 + Задача 5
07 .01.2021 1  Задача 5
Расчет нужен именно в мере, т.к. я сделал отдельную таблицу дат и в ней посчитал это количество.
Но в основной таблице у меня еще куча доп.условий (типы задач, статусы задач), я хочу что бы они в сводной таблице в виде срезов применялись, а для этого нужен расчет в мере.

Спасибо.
 
duhovnik, файл пример приложите.
 
Поле "Пояснение" нужно выводить?
 
Цитата
mechanix 85 написал: Поле "Пояснение" нужно выводить?
Нет, не нужно.
Для пояснения расчета добавил.
Изменено: vikttur - 30.07.2021 11:58:35
 
вот
 
А generatetable обязательно использовать?
Есть ли решение без неё, на основе связей между таблицами?
 
Если видоизменить таблицу Заявок, то можно и через связи или вот еще нашел вариант
Код
=CALCULATE(DISTINCTCOUNT('заявки'[Название задач]);FILTER('заявки';COUNTROWS(FILTER('Календарь';'заявки'[Начало]<=[Дата]&&'заявки'[Окончание]>=[Дата]))>0))
Изменено: mechanix 85 - 27.07.2021 16:41:42
 
Так? Работать будет и на период, а не одну дату. Имя столбца в таблице дат измените на "Дата"
Код
Мера кол-ва задач по дням:=
VAR MinDate = MIN ( 'Даты'[Дата] ) 
VAR MaxDate = MAX ( 'Даты'[Дата] ) 
VAR FilteredTable = 
   FILTER ( 
      'Данные'; 
      'Данные'[Начало] <= MaxDate 
         && 'Данные'[Окончание] >= MinDate 
   ) 
VAR NumOfTasks = CALCULATE ( DISTINCTCOUNT ( 'Данные'[Название задач] ); FilteredTable ) 

RETURN NumOfTasks
Изменено: surkenny - 27.07.2021 13:30:17
 
mechanix 85, не получается, выводит только сумму по началу - см пример в файле.

Цитата
surkenny написал: Так? Работать будет и на период, а не одну дату. Имя столбца в таблице дат измените на "Дата"
У меня PowerPivot для 2013 Excel, такая формула вообще не вставляется.
Изменено: vikttur - 30.07.2021 12:06:06
 
duhovnik, удалите связь с календарем
 
Мне нельзя без связей, т.к. у меня в отчете (основном) есть еще другие метрики которые как раз на основе связей работают.
Или вы имеете в виду что использование связей это не лучшая практика.

Плюс без связей сейчас не отображаются пустые (нулевые) значения. То есть если я эти данные выведу в диаграмму, то у меня разрывов не будет.
 
Не вникал в тему. Решение по вопросу, как отобразить 0 с календарем и связями:
Код
=var mera = 
CALCULATE(
     DISTINCTCOUNT([Задача]);
     FILTER('Задачи';
           COUNTROWS(FILTER('Даты';'Задачи'[Начало]<='Даты'[Даты]&&'Задачи'[Окончание]>='Даты'[Даты]))>0))
return
IF(mera = BLANK();0;mera)
 
Не работают у меня такие функции. Возможно у вас PowerQuery или PowerBI или моя версия экселя не поддерживает такие функции
Изменено: vikttur - 30.07.2021 12:00:47
 
тогда так, суть та же самая:
Код
=IF(
CALCULATE(DISTINCTCOUNT([Задача]);FILTER('Задачи';COUNTROWS(FILTER('Даты';'Задачи'[Начало]<='Даты'[Даты]&&'Задачи'[Окончание]>='Даты'[Даты]))>0))=BLANK();
0;
CALCULATE(DISTINCTCOUNT([Задача]);FILTER('Задачи';COUNTROWS(FILTER('Даты';'Задачи'[Начало]<='Даты'[Даты]&&'Задачи'[Окончание]>='Даты'[Даты]))>0))
)
 
Спасибо, но  работает только если нет связей. А у меня связи между таблицами есть.
Изменено: vikttur - 30.07.2021 12:00:21
 
Цитата
duhovnik написал:
Осталось разобраться как работает
да ничего сложного. В календаре отражены все даты, т.к. на некоторые даты в таблице фактов нет данных, то и выводить нечего, т.е. пустота или BLANK(). Поэтому получается разрыв дат в сводной таблице - это нормально и это правильное поведение модели данных. Т.к. Вам нужно отразить эти пустые даты, то я сделал проверку: если вычисление на дату = пусто, то отобрази мне 0 вместо пустоты. Ну а если значение какое-то есть, то само значение.
Кстати, я забыл - есть же небольшой трюк с настройками: можно в сводной отметить, чтобы отображать пустые строки (правда без нолей) - см. картинку
 
vikttur,
Цитата
duhovnik написал:
Спасибо, но  работает только если нет связей. А у меня связи между таблицами есть.
тут же другое сообщение было и с другим смыслом - контекст моего ответа меняется.
 
Автор и сам мог не плодить сообщения. а отредактировать предыдущее (сначла было: спасибо, вс работает; потом - ан, нет, не работает). Вашего ответа до правки не было
Изменено: vikttur - 30.07.2021 12:12:58
 
Цитата
vikttur написал:
Автор и сам мог не плодить сообщения. а отредактировать предыдущее (сначла было: спасибо, вс работает; потом - ан, нет, не работает). Вашего ответа до правки не было
Да, прошу прощения.
Я открыл пример - посмотрел - всё ОК.
Перенес в свой отчет - не работает. Стал смотреть - у меня есть связи, в примере нет. Снял у себя связи - заработало. Поставил у себя связи - сбилось.
А менять ответ... Может кто-то уже ответит.
 
Цитата
duhovnik написал:
Стал смотреть - у меня есть связи, в примере нет
Ваш косяк. Я саму меру не правил, просто добавил условие в нее IF...THEN...ELSE. Раз она считает неверно, это тогда опять к сообщению 11.
 
duhovnik,  не понимаю, у Вас же в календаре лежит столбец "Кол-во задач" с уже нужным результатом. Почему его не берёте в расчет?
Код
=SUM('Даты'[Кол-во задач])

с включенной связью.
 
Цитата
duhovnik написал:
Спасибо, но  работает только если нет связей. А у меня связи между таблицами есть.
Добрый день!
а если отключить связь для меры через Crossfilter с 3 аргументом none?
Код
CALCULATE (
        SUMX (
            'Данные',
            IF (
                MAX ( 'Даты'[Даты] ) >= 'Данные'[Начало]
                    && MAX ( 'Даты'[Даты] ) <= 'Данные'[Окончание],
                1,
                0
            )
        ),
        CROSSFILTER ( 'Данные'[Начало], 'Даты'[Даты], NONE )
    )
 
Цитата
Vladimir Chebykin написал:
duhovnik ,  не понимаю, у Вас же в календаре лежит столбец "Кол-во задач" с уже нужным результатом. Почему его не берёте в расчет?Код ? 1=SUM('Даты'[Кол-во задач])
Это не совсем корректно с точки зрения итога отчета.

Да, на листе "Даты" я посчитал это количество. Но такие доп. столбцы увеличивают размер отчета. Если у меня в отчете 100 000 строк, то добавление нового столбца увеличивает размер отчета достаточно существенно.
Поэтому я и хочу найти меру в виде формулы, что бы она пересчитывала данные без необходимости создания новых столбцов на других листах.
По сути мне формулу подсчета количества значений с листа "Даты" нужно перенести в меру на листе "Задачи".

Мера должна быть на листе с "Задачами", что бы позднее к сводной таблице (в которой будут выводиться данные) можно было добавить различные срезы: по типу системы, по автору, по исполнителю и т.д.
Насколько я понимаю, если мера не другом листе, то к ней нельзя применить фильтрацию / срезы с первого листа.
 
Доработал чей-то вариант выше (работает со связью):
Код
=CALCULATE(
   DISTINCTCOUNT([Задача]);
   FILTER(ALL('Задачи');
      COUNTROWS(
         FILTER('Даты';
            'Задачи'[Начало]<='Даты'[Даты]&&
            'Задачи'[Окончание]>='Даты'[Даты])
         )>0
   )
)
 
Цитата
Vladimir Chebykin написал:
Доработал чей-то вариант выше (работает со связью):
Владимир, спасибо.
Да, этот вариант считает количество задач корректно.

Мне он не подходит, т.к. при нём не работают срезы (фильтра по доп.столбцам).
Видимо нужно копать куда-то в сторону userralationship

Запрос можно закрывать.
 
Цитата
duhovnik написал:
Мне он не подходит, т.к. при нём не работают срезы (фильтра по доп.столбцам).
Вы можете нормальный пример сделать со всеми доп. условиями! Чтобы не было этого, "к сожалению мне не подходит...у меня еще вот первое, второе и третье.." Достаточно одну функцию заменить в мере, и будут учитывать и срезы в том числе.
 
Цитата
Vladimir Chebykin написал:
Вы можете нормальный пример сделать со всеми доп. условиями!
Ок. Во вложении.
Та же таблица что и раньше + ваша формула.
Нужно что бы работал фильтрация по срезу "Тип системы" (1, 2 или 3).
 
в мере
Код
ALL('Задачи')

замените на
Код
ALLEXCEPT('Задачи';'Задачи'[Тип системы])
 
Цитата
Vladimir Chebykin написал:
замените на Код ? 1ALLEXCEPT('Задачи';'Задачи'[Тип системы])
И так нужно будет прописывать каждый срез.
То есть если у меня 10 разных срезов, то все десять нужно указывать в ALLEXCEPT ?
 
Код
ALLSELECTED('Задачи')

у меня не черный пояс по DAX, поэтому тоже ошибаюсь!
Страницы: 1
Наверх