Customer Activity

[manideep547]

Here I have table A it contains id, date, sector, zone. I created  a calculated column "Customer Relation"  
if the customer has at least one transaction within the last 6 months then considers as an active customer else inactive customer.
HERE IS THE FROMULA 
customer Relation=  if(datediff(Table A[Date],TODAY(),MONTH)>6,"Inactive","Active").

Here my question is, take the input from the date slicer. That means in my dashboard I took date field as slicer visualizer.  For the above formal instated of today() function, use that selected date from date slicer (IN BETWEEN) visualization. 

For Example in data visualization,
If i selected a date from 1-2-2007 to 2-12-2007 (datediff(Table A[Date],1-2-2007,MONTH)>6,"Inactive","Active"))
,if  i selected a date from 1-2-2008 to 2-12-2009 (datediff(Table A[Date],1-2-2008,MONTH)>6,"Inactive","Active"))..etc like that the my report should be change.

[SivaMani]

@manideep547,

Assuming your slicer use this field - Date[Cal Date].

Try the below calculation,

customer Relation=  if(datediff(Table A[Date],MIN(Date[Cal Date]),MONTH)>6,"Inactive","Active")

Hope it will help you!

Thanks,

Siva Mani

LinkeIn

[manideep547]

After selecting the date in the slicer. If I click on active it will show active customers(another visualizer should be change based on active ) and if I click on the inactive then it will show the inactive customers(another visualizer should be change based on active)

After the selection of the date, we click the active and inactive. based on that other cards should be changed. For direct query@sivaMani

[v-lili6-msft · Accepted solution]

hi @manideep547 

First, you should know that:

1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Second, for your case, you could try this way:

Step1:

you need to use two separate tables of date and Customer Activity state for slicer

Step2:

Create a measure that show Customer Activity state for each id dynamically

customer Relation = IF(DATEDIFF(SELECTEDVALUE('Table A'[date]) ,MIN('Date'[Date]),DAY)>6,"Inactive","Active")

 NOTE: you could just replace "DAY" with "MONTH" for this formula, I just use it as a simple sample.

Step3:

Then create a measure that count id that based on Customer Activity state

Result = 
var _table=ADDCOLUMNS('Table A',"_customer Relation",[customer Relation]) return
COUNTAX(FILTER(_table,[_customer Relation]=SELECTEDVALUE('Customer Activity'[State])),[id])

Result:

and here is a sample pbix file, please try it.

Regards,

Lin

[manideep547]

Here I changed day to months and in slicer also I changed the date but it showing the wrong functionality(Active) min date is 1/1/2009 and table date is 1/1/2019 it is showing active