Forum Discussion
Customer Activity
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.
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
4 Replies
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 ManiAfter 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
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
