Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi! I am trying to calculate "active customers" in a given month. I am at a standsi
Here is the data set. I want a distinct count of program numbers but only count those where Customer Enrolled = 1 for a given month. For example, the first two would be counted in Jan, Feb, March, April, May, June, July, August. The 7th one on the table would also count in september and in these other months. If customer enrolled is 0, that program number would not be in any month. I have tried the following.
Program_No | Program Start Date | Program End Date | Customer Enrolled |
P2200399039 | 1/3/2022 | 8/3/2022 | 1 |
P2200336838 | 1/3/2022 | 8/4/2022 | 1 |
P2200347181 | 1/3/2022 | 8/1/2022 | 1 |
P2200484315 | 1/4/2022 | 8/7/2022 | 1 |
P2200449729 | 1/4/2022 | 8/1/2022 | 1 |
P2200416122 | 1/4/2022 | 8/7/2022 | 1 |
P2201432207 | 1/14/2022 | 9/12/2022 | 1 |
P2201768200 | 1/17/2022 | 11/23/2022 | 1 |
P2201773512 | 1/17/2022 | 8/11/2022 | 1 |
P2201753833 | 1/17/2022 | 10/3/2022 | 1 |
P2201735989 | 1/17/2022 | 8/3/2022 | 1 |
P2201846268 | 1/18/2022 | 10/20/2022 | 1 |
P2201869106 | 1/18/2022 | 11/29/2022 | 1 |
P2201867142 | 1/18/2022 | 1/26/2023 | 0 |
P2201842158 | 1/18/2022 | 10/31/2022 | 0 |
P2201813974 | 1/18/2022 | 8/5/2022 | 1 |
P2201868596 | 1/18/2022 | 9/19/2022 | 1 |
P2201898799 | 1/18/2022 | 12/23/2022 | 1 |
P2201813261 | 1/18/2022 | 9/28/2022 | 1 |
P2201846294 | 1/18/2022 | 8/15/2022 | 1 |
P2202054448 | 1/20/2022 | 8/8/2022 | 0 |
P2202077917 | 1/20/2022 | 9/22/2022 | 1 |
P2202030467 | 1/20/2022 | 8/11/2022 | 1 |
P2202539602 | 1/25/2022 | 1/12/2023 | 0 |
P2202676340 | 1/26/2022 | 1/12/2023 | 0 |
P2202644106 | 1/26/2022 | 11/21/2022 | 1 |
P2203333550 | 2/2/2022 | 8/24/2022 | 1 |
P2203540012 | 2/4/2022 | 8/11/2022 | 0 |
P2204193603 | 2/10/2022 | 11/28/2022 | 1 |
P2204549564 | 2/14/2022 | 8/7/2022 | 1 |
P2204581262 | 2/14/2022 | 8/11/2022 | 1 |
P2204532390 | 2/14/2022 | 1/12/2023 | 0 |
P2204741062 | 2/16/2022 | 11/11/2022 | 1 |
P2204876074 | 2/17/2022 | 9/16/2022 | 1 |
P2205349649 | 2/22/2022 | 10/25/2022 | 1 |
P2205435050 | 2/23/2022 | 1/5/2023 | 0 |
Solved! Go to Solution.
@LaurenTSloan does this work ?
Measure =
VAR dates =
CALCULATETABLE (
VALUES ( 'calendar'[Date] ),
ALLEXCEPT ( 'calendar', 'calendar'[YrMo] )
)
VAR __count =
GENERATE (
SUMMARIZE (
FILTER ( 'fact', 'fact'[Customer Enrolled] = 0 ),
'fact'[Program_No],
'fact'[Program Start Date],
'fact'[Program End Date]
),
ADDCOLUMNS (
GENERATESERIES (
CONVERT ( 'fact'[Program Start Date], INTEGER ),
CONVERT ( 'fact'[Program End Date], INTEGER ),
1
),
"Date", CONVERT ( [Value], DATETIME )
)
)
VAR match =
SUMMARIZE ( FILTER ( __count, ( [Date] ) IN dates ), [Program_No] )
RETURN
COUNTROWS ( match )
PFA
Wow! Thank you. I think I am close and this is amazing. I have a few differnt names of tables and dates from my date table so added them here. I got an error when I tried to add this as a count to my visual. The error is:
Couldn’t load the data for the visual.
MdxScript (Model) (28.13) Calculation error in measure “Measure Tables’[Active member test]: The arguments in Generate Series function cannot be blank. thoughts? Below is my draft query.
One of the columns might be blank
GENERATESERIES (
CONVERT ( 'MHK Program'[Program Start Date], INTEGER ),
CONVERT ( 'MHK Program'[Program End Date], INTEGER ),
1
)
@LaurenTSloan does this work ?
Measure =
VAR dates =
CALCULATETABLE (
VALUES ( 'calendar'[Date] ),
ALLEXCEPT ( 'calendar', 'calendar'[YrMo] )
)
VAR __count =
GENERATE (
SUMMARIZE (
FILTER ( 'fact', 'fact'[Customer Enrolled] = 0 ),
'fact'[Program_No],
'fact'[Program Start Date],
'fact'[Program End Date]
),
ADDCOLUMNS (
GENERATESERIES (
CONVERT ( 'fact'[Program Start Date], INTEGER ),
CONVERT ( 'fact'[Program End Date], INTEGER ),
1
),
"Date", CONVERT ( [Value], DATETIME )
)
)
VAR match =
SUMMARIZE ( FILTER ( __count, ( [Date] ) IN dates ), [Program_No] )
RETURN
COUNTROWS ( match )
PFA
Fix on subtotal level
Measure 2 = IF (HASONEVALUE('calendar'[YrMo]), [Measure],CALCULATE(SUMX(ALL('calendar'[YrMo]),[Measure])))
User | Count |
---|---|
14 | |
9 | |
7 | |
7 | |
6 |
User | Count |
---|---|
21 | |
11 | |
10 | |
9 | |
8 |