how to count customer Active or Not Active based on the date filter ?

[Chanleakna123]

Hi all , 

My purpose is to see which Customers Active and Customers who are not Active based on the date filter. i have below report table , If the customer who is last 3 months Purchased , we count Active customers , If Not purchased for the last 3 months , they are In-Active Customers . I will also need to filter by date 

[v-frfei-msft · Accepted solution]

Hi @Chanleakna123,

To create a measure as below.

Measure = 
VAR today =
    TODAY ()
VAR e3 =
    EDATE ( today, -3 )
VAR qty =
    CALCULATE (
        SUM ( 'Table'[qty] ),
        FILTER ( 'Table', 'Table'[Date] <= today && 'Table'[Date] >= e3 )
    )
RETURN
    IF ( qty > 0, "Active >=1", "Not Active <1" )

For more details, please check the pbix as attached.

[amitchandak]

Try something like this

Active or Not =
var _Rolling_3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))  
return
if(isblank(_Rolling_3),"Not Active","Active")

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[MAAbdullah_47]

Hi @amitchandak  can I know what do you means by (Date[Date Filter]) where this field located ? Is it in sales order or in Customer table? Please explain

[Chanleakna123]

hi @amitchandak , i have acheived your result by using the Dax with Measure . 

but now i'd like to go another step , how can i count customers who are "Active" or "Inactive" ? 
and how can i create slicer over these measure? 

[amitchandak]

countx, should work.

Active = countx(filter(sales,[Active or Not]="Active"),cutomer_id)

If not. If possible please share a sample pbix file after removing sensitive information.Thanks.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[TomMartens]

Hey @Chanleakna123 ,

please prepare a pbix that contains sample data but still represents your data model, upload the pbix to onedrive or dropbox and share the link. If you use an Excel file to prepare the sample data, upload the xlsx as well.

Please elaborate a little more on "I will also need to filter by date"

Please explain what "Active >= 1" means, and the expected possible values this flag can hold, of course this is also valid for the Non-Active flags.

Regards,

Tom