Count customers in a period

Anonymous ,

try pasting the below code into a blank query.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYy5DcAwDAN3UW1AFJ3A8CyCijz7zxAZzlcej6S7bFLEoFiVsJ6AptUSCIniso9oUXBGWaYmv/6Ye2s/T37+HPuusOc/fb0h4gI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [customer = _t, startdate = _t, enddate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"customer", type text}, {"startdate", type date}, {"enddate", type date}}),
Period = Table.AddColumn(#"Changed Type", "Period", each Duration.Days([enddate] - [startdate]), Int64.Type),
#"Dates in Period" = Table.AddColumn(Period, "Dates in Period", each List.Dates([startdate],[Period],#duration(1, 0, 0, 0))),
#"Expanded Dates in Period" = Table.ExpandListColumn(#"Dates in Period", "Dates in Period"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates in Period",{{"Dates in Period", type date}}),
#"Inserted Month" = Table.AddColumn(#"Changed Type1", "Month", each Date.MonthName([Dates in Period])),
#"Inserted Year" = Table.AddColumn(#"Inserted Month", "Year", each Date.Year([Dates in Period]), Int64.Type),
MOnthYear = Table.AddColumn(#"Inserted Year", "MOnthYear", each Text.Combine({Text.From([Year], "en-CA"), Text.From([Month], "en-CA")}), type text),
#"Removed Columns" = Table.RemoveColumns(MOnthYear,{"startdate", "enddate", "Period", "Dates in Period", "Month", "Year"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"MOnthYear"}, {{"DistinctCount Customer", each Table.RowCount(Table.Distinct(_)), Int64.Type}})
in
#"Grouped Rows"

you can apply a sort order to the month year column as well.

Appreciate a thumbs up if this helps.

Please accept this as a solution if your question is resolved.

Hi Anonymous 

Your "Total_Customer" table is a calculated table while a calculated table/column cannot show in Power Query Editor. So you cannot use Power Query to get the count from this calculated table. Instead, you have to use DAX to get the result. 

You can add a Date table which has YearMonth column into the model and ensure it is not connected to the calculated table. Then create the following measure.

Measure = COUNTROWS(FILTER('Table','Table'[StartDate]<=MAX('Date'[Date]) && 'Table'[EndDate]>=MAX('Date'[Date])))

Add YearMonth column and above measure into a table visual. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.