Forum Discussion

AlB's avatar
AlB
Community Champion
5 years ago
Solved

Optimizing measure - Segment migration

Hi all, See the attached file for support. We have a simple one-table model: CustomerId Dates Segment 6 03/01/2020 Segment2 7 03/01/2020 Segment2 6 03/02/2...
Show and Tell
  • mwegener's avatar
    mwegener
    5 years ago

     

    Hi AlB ,

     

    try this.

    Net new customers v2 = 
VAR _CurrentDate =
    SELECTEDVALUE (Table1[Dates] )
VAR _PreviousDate =
    CALCULATE (
        MAX ( Table1[Dates] ),
        ALLSELECTED ( Table1[Dates] ),
        Table1[Dates] < _CurrentDate
    )
VAR _CountCustomerIDsThisMonth =
    CALCULATE( 
        DISTINCTCOUNT(Table1[CustomerId]),
        Table1[Dates] = _PreviousDate ||
        Table1[Dates] = _CurrentDate
    )
VAR _CountCustomerIDsPreviousMonth =
    CALCULATE( 
        DISTINCTCOUNT(Table1[CustomerId]),
        Table1[Dates] = _PreviousDate
    )
RETURN
    _CountCustomerIDsThisMonth - _CountCustomerIDsPreviousMonth

     

     

     

mwegener's avatar
mwegener
Most Valuable Professional
5 years ago

 

Hi AlB ,

 

try this.

Net new customers v2 = 
VAR _CurrentDate =
    SELECTEDVALUE (Table1[Dates] )
VAR _PreviousDate =
    CALCULATE (
        MAX ( Table1[Dates] ),
        ALLSELECTED ( Table1[Dates] ),
        Table1[Dates] < _CurrentDate
    )
VAR _CountCustomerIDsThisMonth =
    CALCULATE( 
        DISTINCTCOUNT(Table1[CustomerId]),
        Table1[Dates] = _PreviousDate ||
        Table1[Dates] = _CurrentDate
    )
VAR _CountCustomerIDsPreviousMonth =
    CALCULATE( 
        DISTINCTCOUNT(Table1[CustomerId]),
        Table1[Dates] = _PreviousDate
    )
RETURN
    _CountCustomerIDsThisMonth - _CountCustomerIDsPreviousMonth

 

 

 

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    5 years ago

    HI AlB ,

     

    depending on the underlying data, this adjustment could also help.

    Net new customers v3 = 
VAR _CurrentDate =
    SELECTEDVALUE (Table1[Dates] )
VAR _PreviousDate =
    CALCULATE (
        MAX ( Table1[Dates] ),
        ALLSELECTED ( Table1[Dates] ),
        Table1[Dates] < _CurrentDate
    )
VAR _CountCustomerIDsThisMonth =
    CALCULATE( 
        SUMX(VALUES(Table1[CustomerId]),1),
        Table1[Dates] = _PreviousDate ||
        Table1[Dates] = _CurrentDate
    )
VAR _CountCustomerIDsPreviousMonth =
    CALCULATE( 
        SUMX(VALUES(Table1[CustomerId]),1),
        Table1[Dates] = _PreviousDate
    )
RETURN
    _CountCustomerIDsThisMonth - _CountCustomerIDsPreviousMonth

     

    Got it from the Guy in a Cube video "Debugging a slow Power BI report with Phil Seamark"

    https://www.youtube.com/watch?v=eABg872TAJU