Forum Discussion
Visit Frequency
- 6 years ago
hi manideep547
For your case, you could try this way as below:
Note: if your sample data is wrong, id 3 doesn't have the transaction once every 3 months
Step1:
Add a calculate column that Visit Frequency as below:
Visit Frequency = var _id='Table'[ID] var _date='Table'[Date] var _nextdate=CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[ID]=_id && 'Table'[Date]>_date)) var _Frequency= DATEDIFF(_date,_nextdate,MONTH) return "Once Every" & _Frequency & "Months"Step2:
Drag Visit Frequency column in a visual and set visual level filter to filter 'Once EveryMonths' and drag ID column into visual too.
and here is sample pbix file, please try it.
Regards,
Lin
hi manideep547
For your case, you could try this way as below:
Note: if your sample data is wrong, id 3 doesn't have the transaction once every 3 months
Step1:
Add a calculate column that Visit Frequency as below:
Visit Frequency =
var _id='Table'[ID]
var _date='Table'[Date]
var _nextdate=CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[ID]=_id && 'Table'[Date]>_date))
var _Frequency= DATEDIFF(_date,_nextdate,MONTH)
return
"Once Every" & _Frequency & "Months"
Step2:
Drag Visit Frequency column in a visual and set visual level filter to filter 'Once EveryMonths' and drag ID column into visual too.
and here is sample pbix file, please try it.
Regards,
Lin