Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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
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.
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.