Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Vlookup - repeaters

Hello

 

I try to calculate Repeaters - customers who have requested a cancellation request in the past 12 months, and again, in the last calendar month ( at the time or writing it those dates are: Everyone from 1.02.2019-31.01.2020 and again, in 01.2020. 

The data set sample:

InstallationDate CreatedCommunication No.Year Month Created
330004214-1-2020-2169345

202001

 Installation refers to a customer, communication no- identifies the cancellation request made by customer. Date and year month created both refer to when the cancel request was created in our system.

 

I want each Repeatition to be counted as 1, even if the customer requested to be cancelled 2 or 3 times in January 2020. 

 

Can you help me build the corresponding DAX?

 

Thank you,

Lena

 

 

Data Protection
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Anonymous ,

     

    Please create a measure as below to work on it.

    Measure = 
VAR cou =
    ADDCOLUMNS (
        'Table',
        "c", CALCULATE (
            COUNTROWS ( 'Table' ),
            ALLEXCEPT ( 'Table', 'Table'[Installation] )
        )
    )
RETURN
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Installation] ), FILTER ( cou, [c] >= 2 ) )

     

    If it doesn't meet your requirement,  kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

2 Replies

  • BobBI's avatar
    BobBI
    Icon for Resolver III rankResolver III
    6 years ago

     Customer in last 12 months

    Customer Count ( Last 12 Months) = CALCULATE(COUNT(Table1[Installation ]),
    DATESINPERIOD(Table1[date created],TODAY(),-12,MONTH)
    )

     

     Last calendar month

    Customer Count ( Last Calendar Months) = CALCULATE(DISTINCTCOUNT(Table1[Installation ]),
    DATESINPERIOD(Table1[date created],TODAY(),-1,MONTH)
    )
     
    Hope this would help
    Sukhi

     

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Please create a measure as below to work on it.

    Measure = 
VAR cou =
    ADDCOLUMNS (
        'Table',
        "c", CALCULATE (
            COUNTROWS ( 'Table' ),
            ALLEXCEPT ( 'Table', 'Table'[Installation] )
        )
    )
RETURN
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Installation] ), FILTER ( cou, [c] >= 2 ) )

     

    If it doesn't meet your requirement,  kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.