Измерьте количество цветущих цветов в моем саду
У меня есть несколько видов цветов: цинния, петунии и т. д. Каждый тип цветка цветет только один раз в течение определенного периода, который зависит от сорта цветка. Мне нужна мера, чтобы знать, когда пригласить маму в гости. Я хотел бы построить таблицу, показывающую, сколько цветов будет цвести в данном месяце. Итак, я построил две таблицы. В таблице дат указаны только месяцы от настоящего момента до 20 лет. В таблице цветов есть столбцы для типа цветка, месяца начала цветения, месяца окончания цветения и количества растений этого типа, которые у меня есть. В таблице цветов есть строка для каждого сорта цветов.
Не могли бы вы написать показатель, который покажет мне количество цветов каждого типа, которые будут цвести в разные месяцы таблицы дат в течение следующих нескольких лет? Если в контексте фильтра нет сортов, он должен вернуть общее количество цветущих цветов в этом месяце. Вот входные данные садовника, хотя на самом деле в таблице 85 500 сортов цветов.
Вот что я ожидаю увидеть в качестве вывода в Power BI или Excel/Power Pivot. Пожалуйста, извините за возможные ошибки, так как я делал это вручную. Я бы хотел, чтобы это было автоматизировано, чтобы избежать ошибок в будущем.
Ответы 1
Шаги следующие:
Стартовые данные:
Выберите таблицу и данные — из диапазона таблиц, чтобы добавить их в PQ. Закройте и добавьте в модель данных.
Откройте Power Query и создайте таблицу дат. Вставьте следующий код в пустой запрос с именем Date.
let
Source = { Number.From( #date(min, 1,1) ) .. Number.From( #date(max, 12,31) ) },
min = Date.Year(List.Min( Table1[BloomStart])),
max = Date.Year(List.Max( Table1[BloomEnd])),
#"Convert to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Convert to Table",{{"Date", type date}}),
#"Insert Day Name" = Table.AddColumn(#"Changed Type", "Day Name", each Text.Start(Date.DayOfWeekName([Date]),3), type text),
#"Insert Working Day" = Table.AddColumn(#"Insert Day Name", "Working Day", each
if List.Contains({0..4}, Date.DayOfWeek([Date])) then true else false, type logical),
#"Insert Quarter" = Table.AddColumn(#"Insert Working Day", "Quarter", each "Q" & Text.From( Date.QuarterOfYear([Date])), type text),
#"Insert Fiscal Quarter" = Table.AddColumn(#"Insert Quarter", "Fiscal Quarter", each
let a = Date.QuarterOfYear([Date]),
b = if a > 1 then a - 1 else a + 3
in
"Q" & Text.From( b)),
#"Insert Fiscal Quarter Sort" = Table.AddColumn(#"Insert Fiscal Quarter", "Fiscal Quarter Sort", each let a = Date.QuarterOfYear([Date]),
b = if a > 1 then a - 1 else a + 3
in
b, Int64.Type),
#"Insert Month Name" = Table.AddColumn(#"Insert Fiscal Quarter Sort", "Month Name", each Text.Start(Date.MonthName([Date]),3), type text),
#"Insert Fiscal Month" = Table.DuplicateColumn(#"Insert Month Name", "Month Name", "Fiscal Month"),
#"Insert Year" = Table.AddColumn(#"Insert Fiscal Month", "Year", each Date.Year([Date]), Int64.Type),
#"Insert Month Number" = Table.AddColumn(#"Insert Year", "Month Number", each Date.Month([Date]), Int64.Type),
#"Insert Fiscal Month Number" = Table.AddColumn(#"Insert Month Number", "Fiscal Month Number", each if [Month Number] > 3 then [Month Number] - 3 else [Month Number] + 9, Int64.Type),
#"Insert Fiscal Year" = Table.AddColumn(#"Insert Fiscal Month Number", "Fiscal Year", each let a = Date.Year([Date]),
b = if [Month Number] > 3 then "FY" & Text.End(Text.From( [Year]),4) else "FY" & Text.End(Text.From( [Year]-1),4) in b, type text),
#"Insert End of Month" = Table.AddColumn(#"Insert Fiscal Year", "End of Month", each Date.EndOfMonth([Date]), type date),
#"Insert Day of Month" = Table.AddColumn(#"Insert End of Month", "Day of Month", each Date.Day([Date]), Int64.Type),
#"Insert Day of Week" = Table.AddColumn(#"Insert Day of Month", "Day of Week", each Date.DayOfWeek([Date],1), Int64.Type),
#"Insert Week of Month" = Table.AddColumn(#"Insert Day of Week", "Week of Month", each Date.WeekOfMonth([Date]), Int64.Type),
#"Insert Day of Year" = Table.AddColumn(#"Insert Week of Month", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
#"Insert YYYMM" = Table.AddColumn(#"Insert Day of Year", "YYYYMM", each Number.From( Text.Combine({Date.ToText([Date], "yyyy"), Date.ToText([Date], "MM")})), Int64.Type),
#"Insert Year Month" = Table.AddColumn(#"Insert YYYMM", "Year Month", each Text.Combine({Date.ToText([Date], "MMM"), " ", Date.ToText([Date], "yy")}), type text)
in
#"Insert Year Month"
Закройте и снова загрузите модель данных, только создав соединение. Откройте модель данных, выберите столбец месяца в таблице дат и отсортируйте по номеру месяца.
Перейдите в «Вставка» — «Сводная таблица» — «Из модели данных».
Создайте меру с помощью следующего кода:
=SUMX(
FILTER(Table1, Table1[BloomStart]<= MAX('Date'[Date]) && Table1[BloomEnd]>= MAX('Date'[Date]) ),
Table1[NumPlants]
)+0
Создайте сводную таблицу следующим образом:
Измените форматирование поля InBloom:
Готовая таблица: