Measure to show number of flowers in bloom in my garden

Question

I have several types of flowers: zinnia, petunias, etc. Each type of flower is only ever going to bloom once during a certain period that depends on the flower variety. I'd like a measure so I can know when to invite my mother over to visit. I'd like to construct a table showing how many flowers will be in bloom in a given month. So I constructed two tables. The date table just has the months in it from now to 20 years from now. The flower table has columns for flower type, beginning bloom month, ending bloom month, and number of plants of that type I have. There is a row in the flower table for each flower variety.

Could you write a measure that will show me the number of flowers of each type that will be in bloom during the various months in the date table over the next few years? If there are no varieties in the filter context, it should return the total number of flowers in bloom during that month. Here's the input data from the gardener, although there are actually 85,500 flower varieties in the table.

Here's what I would expect to see as output in Power BI or Excel/Power Pivot. Please excuse any errors, as I did this by hand. I'd like this automated to avoid errors going forward.

Answer 1

Steps as follows:

Start data:

Select table and Data - From Table Range to add to PQ. Close and add to the data model.

Open Power Query and create a date table. Paste the following code into a blank query named 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"

Close and load to the data model again only creating a connection. Open the data model, select the month column in the date table and sort by month number.

Go to Insert - Pivot Table - From Data Model.

Create a measure with following code:

=SUMX(
    FILTER(Table1, Table1[BloomStart]<= MAX('Date'[Date]) && Table1[BloomEnd]>= MAX('Date'[Date])  ), 
    Table1[NumPlants]
)+0

Create a pivot table as follows:

Change the formatting for the InBloom field:

Finished table: