Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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...
Active Customer by Month
  • smpa01's avatar
    smpa01
    3 years ago

    Anonymous  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

     

     

     

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
3 years ago

Anonymous  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

 

 

 

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
3 years ago

Fix on subtotal level

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