Count customers in a period

[Anonymous]

Spoiler

Hi there,

I am finding me lost in counting customers for a period. My data looks like this:

Well, I can count manually, but I have millions of data and have no good idea. Is there a function in Power BI which can calculate how many customers we have in which month?

thank you 

[adudani · Accepted solution]

@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.

[Anonymous]

Hi adudani,

I was able to create the new query with your code. Thanks so much.

My original data is in a table

I am not sure how I can change your code so that it uses my "Total customer" table.

Can you please help me again?

Thank you

[adudani]

@Anonymous ,

Are you able to share the files removing sensitive information?

The reason is because this is a calcuted table.

Else, i would've suggested you update the source step of the solution and this would apply. ( After making required changes in the changed type step)

Let me know thanks

[Anonymous]

Hi adudani,

I have tried to update the source as my data also comes from a calculated table / field

and I replaced the blue marked part

with the table name "Total_Customer"

Seems not to be that easy as I was hoping. Is it even possible to use an existing table as a source?

[v-jingzhang · Accepted solution]

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.

[Anonymous]

Thank you so much. This helps me to understand my issue better. I should get it done now.

Biggest thanks ever

[adudani · Accepted solution]

@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.