Identify New Clients at any time based on a rolling 12 months prior

[rush]

Hi

Please assit with the issue below I have.

I currently have a measure below that calculates new clients based on their last date billed but would like to have it dynamic in the sense that I can select a month in the past and it would give me a list of new clients.

A new client is defined as someone that we have not billed in 12 months prior to their last date billed.

I managed to create a static measure that will give me the result I need but would like for it to be automated on any particular month / year I select.

For April 2018, it would look back 12 months from that period to see if any billing was generated, if not, it would mark it as a 1.

Please find link to sample data below:

Sample Data

New Clients for March 2018 PR = 

VAR Rev_prior_to_last_billing_period = CALCULATE ( [Total Billing PR] , 
Dim_Date[Date_Key] >= 20170301 && Dim_Date[Date_Key] <= 20180228)

VAR Rev_for_March_2018 = 
CALCULATE (
    [Total Billing PR] , Dim_Date[Date_Key] >= 20180301 && Dim_Date[Date_Key] <= 20180331)

RETURN

IF ( AND ( Rev_prior_to_last_billing_period = 0 , 
Rev_for_March_2018 > 0 ) , 1 , 0 )

New Clients for April 2018 = 

    VAR Rev_prior_to_last_billing_period = CALCULATE ( [Total Billing PR] , 
    Dim_Date[Date_Key] >= 20170401 && Dim_Date[Date_Key] <= 20180331)

    VAR Rev_for_April_2018 = 
    CALCULATE (
    [Total Billing PR] , Dim_Date[Date_Key] >= 20180401 && Dim_Date[Date_Key] <= 20180430)

    RETURN

    IF ( AND ( Rev_prior_to_last_billing_period = 0 , Rev_for_April_2018 > 0 ) , 1 , 0 )
    

[v-jiascu-msft]

Hi @rush,

I would suggest you create an independent Date table and establish relationships. The measure could like this. The visual could like the one in the snapshot, which has Date and custom context in your scenario.

New Clients for April 2018 =
VAR Rev_prior_to_last_billing_period =
    CALCULATE (
        [Total Billing PR],
        DATESINPERIOD ( 'DimDate'[Datekey], MAX ( 'DimDate'[Datekey] ), -12, MONTH )
    )
VAR Rev_for_April_2018 = [Total Billing PR]
RETURN
    IF (
        AND ( Rev_prior_to_last_billing_period = 0, Rev_for_April_2018 > 0 ),
        1,
        0
    )

If you still have issues, please provide a dummy sample.

Best Regards,
Dale

[rush]

Hi @v-jiascu-msft

Thank you for the suggestion but the measure does not work.

Please find link to sample data below:

Sample Data

[v-jiascu-msft · Accepted solution]

Hi @rush,

Please download the demo in the attachment. Please use this measure. I also create a measure to verify the result

If New Clients =
VAR Rev_prior_to_last_billing_period =
    CALCULATE (
        SUM ( 'Table1'[Total Billing PR] ),
        DATESINPERIOD (
            'Dim_Date'[Date_Key],
            MIN ( 'Dim_Date'[Date_Key] ) - 1,
            -12,
            MONTH
        )
    )
VAR Rev_for_current =
    SUM ( 'Table1'[Total Billing PR] )
RETURN
    IF ( AND ( Rev_prior_to_last_billing_period = 0, Rev_for_current > 0 ), 1, 0 )

Best Regards,
Dale

[rush]

@v-jiascu-msftThank you very much. It works well based on a selected period.