How to Calculate re-active customer by first billing after 6 months?

[rainchong-7401]

Hi everyone,
I've no idea how to calculate re-active customer 

Expectated result:
-------------------
IF
this customer last 6 months no billing AND this month he/she billed it will +1
ELSE
nothing!

URGENT!! NEED HELP

[v-zhenbw-msft]

Hi @rainchong-7401 ,

We can create two measures to meet your requirement.

1. Create a measure to judge there is value in last 6 months.

Measure = 
var _last1month = ENDOFMONTH(DATEADD('Date'[Date],-1,MONTH))
var _last6month = STARTOFMONTH(DATEADD('Date'[Date],-6,MONTH))
return
CALCULATE(
    SUM('Table'[Amount]),
    FILTER(
        'Table',
        'Table'[Date]<=_last1month && 'Table'[Date]>=_last6month))

2. Then we can create a measure to get the count.

Count cust = 
var _result = CALCULATE(DISTINCTCOUNT('Table'[Cust]),FILTER(SUMMARIZE('Table','Table'[Cust],"Value",[Measure]),[Value]=0))
return
IF(
    ISBLANK(_result),0,_result)

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[rainchong-7401]

I know you're trying to help me but Thanks you didn't understand what Im trying to achieve at all...
-_- ... 

[v-zhenbw-msft]

Hi @rainchong-7401 ,

Do you want to select a specific date, it will show the value of the same day in the last six months?

If yes, you need to create a date table that has no relationship with your original table, then create a measure like this,

Measure = 
var _last1month = DATEADD('Date'[Date],-1,MONTH)
var _last2month = DATEADD('Date'[Date],-2,MONTH)
var _last3month = DATEADD('Date'[Date],-3,MONTH)
var _last4month = DATEADD('Date'[Date],-4,MONTH)
var _last5month = DATEADD('Date'[Date],-5,MONTH)
var _last6month = DATEADD('Date'[Date],-6,MONTH)
return
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Date]=_last1month || 'Table'[Date]=_last2month ||'Table'[Date]=_last3month || 'Table'[Date]=_last4month || 'Table'[Date]=_last5month || 'Table'[Date]=_last6month))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[rainchong-7401]

This is the Expected output I want.
Based on my Selected Month & Year it will checking the last 6 month whether the customers had purchase from us or not either 1 month within last 6 months
if Yes, return nothing
ELSE return count 1.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[rainchong-7401]

@v-zhenbw-msft 
I would need to have a measure that can calculate based on my month selection
If you use the function of TODAY(). It will calculate every last 6 months based on system date/time.

Expected output
------------------
E.g 1:
IF I select Dec-19, So it will calculate last 6 months (Jun-19, Jul-19, Aug-19, Sep-19, Oct-19, Nov-19) of customers who never do billing(<=0 / BLANK()) from us
AND make his/her first payment(>0 / >=1) from us on the *7th month(*Dec-19*)*
COUNT 1
ELSE 
Nothing 

E.g 2:
IF I select Feb-20, So it will calculate last 6 months (Aug-19, Sep-19, Oct-19, Nov-19, Dec-19, Jan-20,) of customers who never do billing(<=0 / BLANK())  from us
AND make his/her first payment(>0 / >=1) from us on the *7th month(*Jan-20*)*
COUNT 1
ELSE
Nothing 

[v-zhenbw-msft]

Hi @rainchong-7401 ,

Does your data structure like this? Do you want to show the count of re-active in a card visual?

We can create a measure to meet your requirement.

Measure = 
var _start = DATE(YEAR(TODAY()),MONTH(TODAY())-6,1)
var _end = DATE(YEAR(TODAY()),MONTH(TODAY()),1)
return
DISTINCTCOUNT('Table'[Customer]) - CALCULATE(DISTINCTCOUNT('Table'[Customer]),FILTER(ALLSELECTED('Table'),'Table'[Date]<_end && 'Table'[Date]>=_start))

All users who purchased minus users who purchased within six months.

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.