Customers who is not buying

[willpioli]

Im actually trying to build a report that shows all custmers that are on the list but doest have a register in the last xx months.

My main table contais the DATE of pursharse, customer name, product name, value.

So, what i want is to create a list of customers who had brought in the las 12 months and didnt brough on the last 3 months for example, and i want to make it works with some filters where i can select the period the customers brought and the period whitch they are not buying.

I tryed some formulas like:

Faturamento No periodo em que comprou = 
IF (
    MIN ( 'Date'[Date] )
        <= CALCULATE ( MAX ( BaseFat2[DataFat] ); ALL ( BaseFat2 ) );
    CALCULATE (
        SUM ( BaseFat2[Valor Liquido Gerencial] );
        FILTER (
            ALL ( 'BaseFat2'[DataFat]);
            BaseFat2[DataFat] <= MAX ( BaseFat2[DataFat] )
        )
    )

 and this

Faturmento no Periodo em que não comprou = 
CALCULATE (
    SUM ( BaseFat2[Valor Liquido Gerencial] );
    FILTER (
        ALL ( BaseFat2[DataFat] );
       -- DATEADD(BaseFat2[DataFat];-12;MONTH)
		 'BaseFat2'[DataFat] <= MAX (BaseFat2[DataFat])
    )
)

ty!!

[v-jiascu-msft]

@willpioli

Hi,

Maybe you can check these thing below. What’s the problem exactly? Could you please post a sample with the relationship here?

1. These tables should have proper relationships.
2. The columns of the filters should come from proper table.

Best Regards!

Dale

[willpioli]

I just have a full table with all my data:

order date,

invoice n,

product,

customer,

value

and another calendar table:

Date = 
ADDCOLUMNS (
CALENDAR (DATE(2009;1;1); DATE(2017;12;31));
"DateAsInteger"; FORMAT ( [Date]; "YYYYMM" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "MM/YYYY" );
"YearMonthShort"; FORMAT ( [Date]; "MMM/YYYY" );
"MonthNameShort"; FORMAT ( [Date]; "MMM" );
"MonthNameLong"; FORMAT ( [Date]; "MMMM" );
--"DayOfWeekNumber"; WEEKDAY ( [Date] );
--"DayOfWeek"; FORMAT ( [Date]; "dddd" );
--"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" )
)

I dont need to know the new custumers, just who bought in a certain period (has any register with orderdate in the period selected) and is had not bought in any other certain period.

Ex.:

Customers who bought in the last 2 years (has a invoice in the last 2 years) but didnt bought in the last 3 months (dont have any invoice in the period)

I´ve tryed the formulas i posted on the first msg, and tryed to use the formulas on the http://www.daxpatterns.com/new-and-returning-customers/ but no sucess....

[v-jiascu-msft · Accepted solution]

@willpioli

Hi,

You can try this formula as a measure.

Check =
VAR TwoYears =
    CALCULATE (
        COUNTA ( 'Table1'[Invoice] );
        DATESINPERIOD ( 'Date'[Date]; TODAY (); -2; YEAR )
    )
VAR ThreeMonth =
    CALCULATE (
        COUNTA ( 'Table1'[Invoice] );
        DATESINPERIOD ( 'Date'[Date]; TODAY (); -3; MONTH )
    )
RETURN
    IF ( ISBLANK ( TwoYears ) = FALSE () && ISBLANK ( ThreeMonth ) = TRUE (); 1; 0 )

The reason of using "Counta" is to avoid the invoice with 0 value. You can use Sum(value) or other functions.

Best Regards!

Dale

[lalthan]

Check this page (Link below)

New and Returning Customers

[willpioli]

this formulas are not working on power bi

[Greg_Deckler]

That's too vague, what is not working?