Active and Inactive customers

[manideep547]

Hi all, 
I need active customers count and inactive customers count Active customers are within the 6 months and Inactive customers are +6months 
table A
id     date   
1        1/1/2018     
1       1/1/2019

2        1/2/2018 

2        1/12/2018

3       1/1/2016

3         1/1/2017

4         1/1/2015

4         1/8/2015
1          1/8/2017

when 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 1
here I try the formula but getting error 

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]

First of all .date is only need when you have a timestamp. Second use a date table.  3 formulas, I am giving. Third one should work

//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)

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.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://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-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[manideep547]

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

[amitchandak]

missed [ ]

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

[manideep547]

@amitchandak 
can you please check my attachment it getting blank.
https://drive.google.com/open?id=15iTjxrsEJVvzBeczc1d9ByvyNF178ZUi
Here I need the count of active and count of inactive customers based on the date selected in the slicer  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. 

[amitchandak]

Date was having timestamp. Created date from that and join. Check now

https://www.dropbox.com/s/lvppqvumfda3ihd/activecustomer.pbix?dl=0

[manideep547]

 A formula with datediff with earlier function  Dax because if I selected active in slicer the count should be an active customer and if I selected inactive it display inactive.  Is it possible to do that ?? @amitchandak 
something like this 

IF(DATEDIFF(SELECTEDVALUE(EDW_GIFTCARDS_CARDHOLDERS[TransactionDt]),EARLIER(EDW_GIFTCARDS_CARDHOLDERS[TransactionDt]),MONTH>6,"Inactive","Active"))

[amitchandak]

Did not got the last update, if you are looking for slicer to choose measure refer

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

[manideep547]

IF(DATEDIFF(SELECTEDVALUE(EDW_GIFTCARDS_CARDHOLDERS[TransactionDt]),EARLIER(EDW_GIFTCARDS_CARDHOLDERS[TransactionDt]),MONTH>6,"Inactive","Active"))
i need someting like above datediff for current date and previous date different is having >6 is active and < 6 is Inactive
@amitchandak