Active and Inactive customers

Question

Hi all,&nbsp;I need active customers count and inactive customers count Active customers are within the 6 months and Inactive customers are +6months&nbsp;table Aid&nbsp; &nbsp; &nbsp;date&nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; 1/1/2018&nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp;1/1/20192&nbsp; &nbsp; &nbsp; &nbsp; 1/2/2018&nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; 1/12/20183&nbsp; &nbsp; &nbsp; &nbsp;1/1/20163&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/1/20174&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/1/20154&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/8/20151&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/8/2017when I filter the data in slicer from 1/1/2018 to 1/1/2019, here Active customers id is 1 and count is 1 and Inactive customers are 2 and count is 1here I try the formula but getting error&nbsp;CALCULATE(
      DISTINCTCOUNT( TableA[ID] )   ),
    DATESINPERIOD( TableA[DATE].[Date], MAX( TableA[DATE].[Date] ), -6, MONTH )
)A date column containing duplicate dates as specified in the call to function 'DATESINPERIOD'.

amitchandak · Answer

First of all .date is only need when you have a timestamp. Second use a date table.&nbsp; 3 formulas, I am giving. Third one should work&nbsp;//1
CALCULATE(
      DISTINCTCOUNT( TableA[ID] )   ),
    DATESINPERIOD( TableA[DATE], MAX( TableA[DATE] ), -6, MONTH )
)
//2

CALCULATE(
      DISTINCTCOUNT( TableA[ID] )   ),
    DATESINPERIOD( DATE[DATE], MAX( DATE[DATE] ), -6, MONTH )
)
//3
sumx(summarize(calculatetable(TableA,DATESINPERIOD( DATE[DATE], MAX( DATE[DATE] ), -6, MONTH )),
TableA[ID] , "_active",distinctcount(TableA[ID])),_active)&nbsp;To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.Referhttps://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions https://www.archerpoint.com/blog/Posts/creating-date-table-power-bihttps://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/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 -Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trendPower-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-RangesConnect on Linkedin

manideep547 · Answer

amitchandak&nbsp;for 3 formula getting error parameter is not the correct type.cannot find the name _active

amitchandak · Answer

missed [ ]sumx(summarize(calculatetable(TableA,DATESINPERIOD( DATE[DATE], MAX( DATE[DATE] ), -6, MONTH )),
TableA[ID] , "_active",distinctcount(TableA[ID])),[_active])

manideep547 · Answer

amitchandak&nbsp;can you please check my attachment it getting blank.https://drive.google.com/open?id=15iTjxrsEJVvzBeczc1d9ByvyNF178ZUiHere I need the count of active and count of inactive customers based on the date selected in the slicer&nbsp;&nbsp;I need to calculate active users based on a 6month window. It would have to go back 6months from month selected date in a slicer.&nbsp;

amitchandak · Answer

Date was having timestamp. Created date from that and join. Check nowhttps://www.dropbox.com/s/lvppqvumfda3ihd/activecustomer.pbix?dl=0&nbsp;&nbsp;

Forum Discussion

Active and Inactive customers

8 Replies

Related content

relationship active & inactive

Active and Inactive customer

Active & Inactive customers

Active and Inactive Relationship - Display Chart on Inactive Relationship

Creating ICMs via Activator