Help Building Query

[CmdrKeene]

I need some help figuring out what strategy I should use to accomplish this in Power BI.

I have a list of subscribed customers along with their subscription start and end dates.  In Excel I can use a COUNTIF function in a separate date table (seen on right side) to count how many customers were active in any given month.  That is, I compare how many customers had a subscription start on/before a given date and also had an end date at some point after that date.  That way I can show subscribers per month as shown.

But I want to do this with Power BI instead of a table to the side.

I'm sure I'll need a date dimension table, and there's plenty of guides on that.  But what about counts per month?  And I'd love to still be able to slice or chart by group (as a legend value in a stacked bar for example).

I've been trying for a while but I'm not getting very far.  Any suggestions for a Power BI newbie?

This file is shared here: CustomerCounts.xlsx

[v-shex-msft]

Hi @CmdrKeene,

You can use below formula if it works on your side:

Active Customer=
var currDate=MAX(DateTable[Date])
return
COUNTAX(FILTER(ALL(BilingItems),AND(BilingItems[StartDate]<=currDate,BilingItems[StopDate]>currDate)&&BilingItems[BILING ITEM]="SUBSCRIBER"),BilingItems[Customer])

Regards,

Xiaoxin Sheng

[Anonymous]

@CmdrKeene,

A Date table will certainly help.  Check these posts for a guide on counting "active" things:

https://community.powerbi.com/t5/Desktop/Count-of-active-customers-line-graph/td-p/56019

https://www.powerpivotpro.com/2013/04/counting-active-rows-in-a-time-period-guest-post-from-chris-ca...

[CmdrKeene]

Thanks a ton for these links, look so incredible I can't wait to dig in.  I haven't used PowerPivot much: PowerQuery has been enough to get/transform almost any data I've needed so far.

[ImkeF · Accepted solution]

If you like, you can do this without a calendar-table and within the query-editor.

Either reference or transform your table like this:

let
    Source = BillingItems,

// Create a list of (first of) months for each entry
    AddMonths = Table.AddColumn(Source, "Months", each List.Distinct(List.Transform({Number.From([StartDate])..Number.From([StopDate])}, each Date.StartOfMonth(Date.From(_))))),

// Expand that list
    #"Expanded Months" = Table.ExpandListColumn(AddMonths, "Months"),

// Add month-column
    AddMonthColumn = Table.AddColumn(#"Expanded Months", "Month", each Date.Month([Months])),

// Add Year-column
    AddYearColumn = Table.AddColumn(AddMonthColumn, "Year", each Date.Year([Months]))
in
    AddYearColumn

This gives you a table with one row per month and subscription. Just choose a count on the field in the values-section then.

[CmdrKeene]

Xiaoxin Sheng - I'm not sure where I'd use your formula. Can you point me in the right direction for your option?

Steve Wheeler - Haven't tried yet but thanks for those great posts.

Imke Feldmann - I love your method of referencing my existing table and your code code is very awesome (and reproduced beauitufully).  I struggled for over an hour trying to read and understand it via email (without line breaks or indents in the code syntax!) I feel like a moron.  Now that I come to the site and read it, it makes sense.  The ExpandListColumn is a nifty trick indeed.