Forum Discussion

ctashwin's avatar
ctashwin
Frequent Visitor
5 years ago
Solved

Visitation Counter

Hi All,   I have a Customer Table with Customer Visitation date and Customer ID. I need to setup a visitaion counter/Number for each of the Customer Visit.    I also would require the Visitaion c...
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi ctashwin ，

     

    You need a measure instead:

    Measure = 

RANKX(FILTER(ALLSELECTED('Table'),'Table'[Customer ID ]=MAX('Table'[Customer ID ])),CALCULATE(MAX('Table'[Visit Date])),,ASC)

    And you will see:

    The counter will be changed by the selection of dates.

    For the related .pbix file,pls see attached.

     

     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
ctashwin's avatar
ctashwin
Frequent Visitor
5 years ago

Hi Tom,

 

Thanks for the reply.

 

The caclulated column works, but the issue i am facing is i have 2 years worth of data and calculated column creates a counter from day 1 in this case ie if a customer is regular  and visits twice a month, his visit counter would give me like 24 for this month. 

 

I am trying to achieve a similar thing, where when i select the last 3 months, the visitaion counter should reset to 1 and start coutning forward.

 

Thanks for the help


Ashwin

 

 

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

ctashwin - Try:

Column =
  COUNTROWS(FILTER('Table',[Customer ID] = EARLIER([Customer ID]) && [Visit Date] <= EARLIER([Visit Date]) && [Visit Date]>=TODAY()-90 ))

Measure
  VAR __CustomerID = MAX([Customer ID])
  VAR __VisitDate = MAX([Visit Date])
RETURN
  COUNTROWS(FILTER('Table',[Customer ID] = __CustomerID && [Visit Date] <= __VisitDate && [Visit Date]>=TODAY()-90 ))