cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

Calculate Active Members

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.

Cummulative Distinct Count = TOTALYTD(DISTINCTCOUNT('Table'[Program_No]),d_Date[CalendarDate])
Terminations Unitl Previous Month = TOTALYTD(CALCULATE(DISTINCTCOUNT('Table'[Program_No]),USERELATIONSHIP(d_Date[CalendarDate],'MHK Program'[Program End Date]),DATEADD(d_Date[CalendarDate],-1,MONTH)),d_Date[CalendarDate])
Active Members = [Cummulative Distinct Count]-[Terminations Unitl Previous Month]

 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
1 ACCEPTED SOLUTION
Super User

@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]
),
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

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
4 REPLIES 4
Regular Visitor

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.

Active member Test =
Var Dates =
CALCULATETABLE (
VALUES ( d_Date[CalendarDate]),
ALLEXCEPT ( d_Date, d_Date[CalendarMonthDesc] )
)
VAR __count =
GENERATE (
SUMMARIZE (
FILTER ( 'MHK Program', 'MHK Program'[Members Enrolled] = 0 ),
'MHK Program'[Program_No],
'MHK Program'[Program Start Date],
'MHK Program'[Program End Date]
),
GENERATESERIES (
CONVERT ( 'MHK Program'[Program Start Date], INTEGER ),
CONVERT ( 'MHK Program'[Program End Date], INTEGER ),
1
),
"Date", CONVERT ( [Value], DATETIME )
)
)
VAR match =
SUMMARIZE ( FILTER ( __count, ( [Date] ) IN dates ), 'MHK Program'[Program_No] )
RETURN
COUNTROWS ( match )
Super User

One of the columns might be blank

``````        GENERATESERIES (
CONVERT ( 'MHK Program'[Program Start Date], INTEGER ),
CONVERT ( 'MHK Program'[Program End Date], INTEGER ),
1
)``````
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Super User

@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]
),
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

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Super User

Fix on subtotal level

``Measure 2 = IF (HASONEVALUE('calendar'[YrMo]), [Measure],CALCULATE(SUMX(ALL('calendar'[YrMo]),[Measure])))``

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.