cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LaurenTSloan
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_NoProgram Start DateProgram End DateCustomer Enrolled
P22003990391/3/20228/3/20221
P22003368381/3/20228/4/20221
P22003471811/3/20228/1/20221
P22004843151/4/20228/7/20221
P22004497291/4/20228/1/20221
P22004161221/4/20228/7/20221
P22014322071/14/20229/12/20221
P22017682001/17/202211/23/20221
P22017735121/17/20228/11/20221
P22017538331/17/202210/3/20221
P22017359891/17/20228/3/20221
P22018462681/18/202210/20/20221
P22018691061/18/202211/29/20221
P22018671421/18/20221/26/20230
P22018421581/18/202210/31/20220
P22018139741/18/20228/5/20221
P22018685961/18/20229/19/20221
P22018987991/18/202212/23/20221
P22018132611/18/20229/28/20221
P22018462941/18/20228/15/20221
P22020544481/20/20228/8/20220
P22020779171/20/20229/22/20221
P22020304671/20/20228/11/20221
P22025396021/25/20221/12/20230
P22026763401/26/20221/12/20230
P22026441061/26/202211/21/20221
P22033335502/2/20228/24/20221
P22035400122/4/20228/11/20220
P22041936032/10/202211/28/20221
P22045495642/14/20228/7/20221
P22045812622/14/20228/11/20221
P22045323902/14/20221/12/20230
P22047410622/16/202211/11/20221
P22048760742/17/20229/16/20221
P22053496492/22/202210/25/20221
P22054350502/23/20221/5/20230
1 ACCEPTED SOLUTION
smpa01
Super User
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]
        ),
        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 )

 

smpa01_0-1676407141850.png

 

PFA

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

4 REPLIES 4
LaurenTSloan
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]
        ),
        ADDCOLUMNS (
            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 )

One of the columns might be blank

 

        GENERATESERIES (
                CONVERT ( 'MHK Program'[Program Start Date], INTEGER ),
                CONVERT ( 'MHK Program'[Program End Date], INTEGER ),
                1
            )




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


smpa01
Super User
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]
        ),
        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 )

 

smpa01_0-1676407141850.png

 

PFA

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Fix on subtotal level

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

 

smpa01_0-1676407739010.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors