Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
PowerPivot - мера для вычисление разницы между двумя датами., Мера по вычислению отклонения между последней и первой датой месяца.
 
#1
12.02.2018 10:01:03

Уважаемые форумчане!

Прошу вашей помощи в решении непростой для меня задачи, но, возможно, элементарной для кого-то из вас.

Помогите в PowerPivot прописать формулу (меру) для вычисления разницы между последней отчетной датой месяца и  первой отчетной датой месяца. В самой сводной, будут несколько дат (соответственно), но хотелось бы как-то софрмировать вывод данный (разницы) между последней дато и  первой отдельным столбцом после последней даты месяца. Как смог описал ситуацию.

Я прописал меру

Код
LDATEvsFDATE:=CALCULATE(SUM('Склад_период'[Показатель]); 'Склад_период'[Date_dm]="31.янв.")/COUNTROWS(DISTINCT('Склад_период'[Date_dm]))-CALCULATE(SUM('Склад_период'[Показатель]); 'Склад_период'[Date_dm]="01.янв.")/COUNTROWS(DISTINCT('Склад_период'[Date_dm]))
" но она немного не соответствует тому, что хотелось бы. Моя меры выводит отклонение м/д двумя датами, последней и первой, но её нужно бутед переписывать под каждый месяц и она выводит столбец, в каждой дате месяца, но он пустой, и только заполнен в первой дате и последней данными вычисленным отклонением.

Изменено: tmhashNIK - 12.02.2018 11:29:02
 
 
 
#2
12.02.2018 11:01:00
Пример, у вас конечно интересный. Количество в одну дату, суммы в другую. В результате нет ни одной ситуации, когда в одном месяце присутствовали бы разные даты по строкам с суммами. Т.е. строка всегда сама является и первой и последней. Ну а результат как вы понимаете будет равен нулю  ;)
Короче, если по-простому, то формулу предлагаю такую:
Код
Разность:=   
         SUMX(
            FILTER(
               'Склад_период';
               [Date]=MAX('Склад_период'[Date]));
               [Показатель]) -
         SUMX(
            FILTER(
               'Склад_период';
               [Date]=MIN('Склад_период'[Date]));
               [Показатель])

И еще здесь формулы полагается выделять тегом CODE. Предлагаю исправить первое сообщение пока модераторы не пришли.
Честно говоря не очень понял, что считает ваша формула меры, но если модифицировать её, под то что у вас не получается то выйдет вот так:
Код
LDATEvsFDATE:=CALCULATE(
   SUM('Склад_период'[Показатель]); 
   FILTER(
      'Склад_период';
      'Склад_период'[Date]=MAX('Склад_период'[Date]))) /
COUNTROWS(
   FILTER(
      'Склад_период';
      'Склад_период'[Date]=MIN('Склад_период'[Date])))-
CALCULATE(
      SUM('Склад_период'[Показатель]); 
      FILTER(
         'Склад_период';
         'Склад_период'[Date]=MIN('Склад_период'[Date]))) /
COUNTROWS(
   FILTER(
      'Склад_период';
      'Склад_период'[Date]=MIN('Склад_период'[Date])))

Так в последней мере РР ругается на использовании формулы MAX в логическом выражении в фильтре. Надо поправить. Поправил.
Изменено: PooHkrd - 12.02.2018 11:36:52
Вот горшок пустой, он предмет простой...
 
 
 
#3
12.02.2018 11:37:02
PooHkrd, я просто чистил (облегчал пример) и наверное удалил часть сумм или количество из дат. Конечно, в дату которая имеет суммму или количество присутствует и оба показателя (сумма, количество). Пример доработал! Сейчас, с учетом нового примера Ваша формула будет такой же?
 
 
 
#4
12.02.2018 11:43:43
Какая будет формула зависит от постановки задачи, на сколько я понял ваш пример формулы, вам нужно посчитать среднее значение по строкам за последнюю и первую отчетные даты и между ними посчитать разность это так? Если да, то в моей первой формуле просто заменить SUMX на AVERAGEX
Вот горшок пустой, он предмет простой...
 
 
 
#5
12.02.2018 12:12:26
PooHkrd, необходимо не среднее значение а разность между фактом. Я снова доработал пример. Посмотрите пожалуйста.
Ваши формулы прописал, но они выводят значение 0. Возможно потому, что необходимо считать по столбцу (SUM), а не по строке (SUMX)
Изменено: tmhashNIK - 12.02.2018 12:16:00
 
 
 
#6
12.02.2018 12:23:13
Результаты отличные от нуля данная мера покажет только в промежуточных результатах для группировки по месяцам. Смотрите пример. Ну и в общих итогах тоже. А для группировки по дням в каждой ячейке сама сводная задает такие фильтры, что мера выдает только результат = 0. Можно в мере прописать, чтобы она убирала воздействие фильтров сводной по полю Date, тогда в каждой ячейке будет результат по всему месяцу.
Изменено: PooHkrd - 12.02.2018 12:26:11
Вот горшок пустой, он предмет простой...
 
 
 
#7
12.02.2018 15:59:05
PooHkrd, спасибо Вам! Всё работает чётко! Подскажите, есть ли возможность как то убрать вывод данной разницы из столбцов дат? А то, нулевые выводятся в каждой дате месяца.
Спасибо!
 
 
 
#8
12.02.2018 16:14:00
Код
=
CALCULATE (
    CALCULATE ( SUM ( 'Таблица1'[Показатель] ); LASTDATE ( 'Таблица1'[Date] ) )
        - CALCULATE ( SUM ( 'Таблица1'[Показатель] ); FIRSTDATE ( 'Таблица1'[Date] ) );
    'Таблица1'[Руб./Ед.] = "Сумма. руб."
)
F1 творит чудеса
 
 
 
#9
12.02.2018 16:15:00
tmhashNIK, через инструмент "Наборы". В файле PooHkrd есть пример.
F1 творит чудеса
 
 
 
#10
12.02.2018 16:32:04
Максим Зеленский, а как эти наборы добавить?
Не смог разобраться(
Спасибо!

Разобрался)! Спасибо!
Изменено: tmhashNIK - 12.02.2018 16:34:15
 
 
 
#11
12.02.2018 17:52:44
Цитата
tmhashNIK написал:
Всё работает чётко!
А вот и нет!  :(  Не четко. Используйте лучше формулу от Максим Зеленский, он там поставил фильтр по полю 'Таблица1'[Руб./Ед.], а у меня его нет, из-за чего в промежуточных итогах по месяцу в суммировании участвуют как рубли так и штуки.
З.Ы. Модифицировал свой вариант:
Код
Разность:=
         SUMX(
            FILTER(
               'Склад_период';
               [Date]=MAX('Склад_период'[Date]) && [Руб./Ед.]="Сумма, руб.");
               [Показатель]) -
         SUMX(
            FILTER(
               'Склад_период';
               [Date]=MIN('Склад_период'[Date]) && [Руб./Ед.]="Сумма, руб.");
               [Показатель])
Изменено: PooHkrd - 12.02.2018 18:01:13
Вот горшок пустой, он предмет простой...
 
 
 
#12
12.02.2018 20:14:33
PooHkrd, Максим Зеленский, спасибо Вам!
 
 
 
Страницы: 1
Читают тему
Наверх