Разница между двумя датами с использованием математики
Я понимаю, что вы неохотно меняете свою модель. Проблема здесь заключается в том, что вы пытаетесь использовать высоко нормированную таблицу и использовать ее для анализа с помощью инструмента OLAP. OLAP-инструменты предпочитают схемы фаз / дим-звезд, а Tabular / PowerBI ничем не отличается. Я подозреваю, что это будет продолжаться и с будущими требованиями.
Это не означает, что вы не можете делать то, что хотите, используя инструменты, но результирующий dax будет менее эффективным, и требуемое хранилище будет неоптимальным.
Итак, с этой оговоркой / лекцией (!) вот как вы можете это сделать.
op_rate_agg =
VAR pivoted =
ADDCOLUMNS (
SUMMARIZE ( 'Query1', Query1[COUNTRY], Query1[DATE] ),
"op_rate", CALCULATE ( AVERAGE ( Query1[Value] ), Query1[ITEM] = "op_rate" ),
"proc", CALCULATE ( SUM ( Query1[Value] ), Query1[ITEM] = "proc" )
)
RETURN
DIVIDE ( SUMX ( pivoted, [op_rate] * [proc] ), SUMX ( pivoted, [proc] ) )
Это действительно неэффективно, так как вам нужно создать свой шарнирный набор для каждого исполнения, и вы увидите, что план запроса должен выполнять намного больше работы, чем если бы вы сохраняли это как соответствующую таблицу фактов. Если ваша модель велика, у вас, вероятно, будут проблемы с производительностью с этой мерой и любые ссылки на нее.
3 ответа
Давайте попробуем написать это формально:
f(y,m,d) = 365*y+ y/4 - y/100 + y/400 + (153*m- 457)/5 + d - 306
date_diff(y1,m1,d1,y2,m2,d2) = f(y1,m1,d1) - f(y2,m2,d2)
Упрощение:
date_diff = (y1-y2)*(365+1/4-1/100+1/400) + (m1-m2)*30.6 + (d1-d2)
И это имеет смысл, потому что:
- У нас есть високосный год каждые 4 года, если только год не делится на 100, а не на 400.
- Среднее количество дней в месяце составляет ~ 30,6
- Все странные магические числа (306 и 457) уходят
Это не полный ответ, но какое-то объяснение.
Прежде всего,
, если число месяца меньше 3, year- = 1 и month + = 12
, используется для эффективного начала года с марта перемещая крайне нерегулярный февраль к концу. На самом деле это то, как этот календарь был изначально разработан, который вы все еще можете увидеть в названиях некоторых месяцев, таких как октябрь или декабрь (вы, вероятно, слышали, что октябрь = 8 и декабрь = 10)
Удар
365*year + year/4 - year/100просто обрабатывает високосные годы.Часть
dayтакже понятна.Теперь давайте удалим биты, которые не имеют значения, когда мы вычтем два значения и получим это значение для
month30*month + (3*month - 2)/5. Я не знаю логику, как это было получено (у меня есть только предположение в конце) но я могу сказать, почему это работает. Бит
30*monthочевиден. И теперь, когда мы переместили февраль на его последнюю позицию, все месяцы в середине года имеют либо30, либо31день. Запишем их:
- Март - 31 - +1
- Апр - 30 - +0
- Май - 31 - +1
- Июнь - 30 - +0
- июль - 31 - +1
- август - 31 - +1
- сентябрь - 30 - +0
- октябрь - 31 - +1
- ноябрь - 30 - +0
- декабрь - 31 - +1
- январь - 31 - +1
мы не волнует февраль, так как после него нет месяцев (мы заботились об этом раньше). Мы заботимся только об этом +1 дне со следующего месяца после этого!
Так что нам сейчас нужна некоторая формула, которая бы соответствовала этому распределению
+1и+0. И кажется, что значение(3*month - 2)/5с целочисленным делением делает именно это. Вы можете легко проверить это вручную.
Вероятно, способ придумать эту идею состоял в том, чтобы заметить, что год (кроме февраля) следует за следующим 5-месячным циклом
+1 +0 +1 +0 +1
Я имею в виду, что март-июль и август-декабрь точно совпадают, и затем январь снова +1, так что это похоже на первый элемент следующего цикла.
Единственная «забавная» часть заключается в том, что если вы вычислите (153*m-457)/5 для значений от 3 до 15, вы получите
0, 31, 61, 92, 122, 153, 184, 214, 245, 275, 306, 337
, где вычитая каждый элемент из предыдущего, начиная со второго, мы получаем [ 114]
31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 31
- количество дней в каждом месяце, если мы начинаем с марта
. Итак, идея такова: перевернитесь, чтобы «странный» февральский месяц был последним, подсчитайте годы, високосные дни и дни за прошедшие месяцы с использованием «магической» формулы, чтобы получить чистое число, увеличивающееся на единицу за каждый день.
Вычитание двух из этих чисел дает разницу в дате.
PS: обратите внимание, что даже «регулярное» вычисление будет O (1)