Check if Monthnumber is in FROM TO Date intervals

[Rygar]

Hi Community,

my clients buy licences, starting 1st of a month and ending at the end of a month.

I need to know how many licences are active each month. Example File

(Solution in Excel is no problem, but I need a solution in Power Query)

Many thanks and best regards,

Michael

[ronrsnfld · Accepted solution]

Here is one way to do this just in Power Query:

 - Create a list of the included month numbers {from..to} and convert to a table

 - Create a list of the month numbers {1..12} and convert to a table

 - Join the two tables with the month number as primary

 - Convert Column 2 to either a One or a Zero depending on the presence/absence of null

 - Group by month number and Sum

 - Sort the result

let
    Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type text}, {"from", type date}, {"to", type date}}),

//create list of months
    monthsList= List.Generate(
        ()=>[mnths={Date.Month(#"Changed Type"[from]{0})..Date.Month(#"Changed Type"[to]{0})}, idx = 0],
        each [idx] < Table.RowCount(#"Changed Type"),
        each [mnths={Date.Month(#"Changed Type"[from]{[idx]+1})..Date.Month(#"Changed Type"[to]{[idx]+1})}, idx = [idx]+1],
        each [mnths]
    ),
    mnthTable = Table.FromColumns(
        {List.Combine(monthsList)},
        type table[Months Active=Int64.Type]),
    allMonths = Table.FromColumns({{1..12}}, type table[Month=Int64.Type]),
    joinedTable = Table.Join(allMonths,"Month",mnthTable,"Months Active",JoinKind.LeftOuter),

//Replace all numeric entries in Months Active with a 1, and nulls with a 0
    xFormMonthsActive=Table.TransformColumns(joinedTable, {"Months Active", each if _<>null then 1 else 0}),
    #"Grouped Rows" = Table.Group(xFormMonthsActive, {"Month"}, {{"Number of Licenses", each List.Sum([Months Active]), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Month", Order.Ascending}})
in
    #"Sorted Rows"

[Rygar]

One more great solution, thanks a lot! 

[ronrsnfld]

Please note that if the span of dates is more than twelve months, the months will be aggregated irrespective of the year.  If you want the years to also be differentiated, you'll need to include that information in your lists.