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!
TomMartens's avatar
TomMartens
Super User
5 years ago

Hey ctashwin ,

 

based on the sample data you provided this calculated column creates the expected result:

Column = 
RANKX(
    CALCULATETABLE(
        SUMMARIZE(
            'Table'
            , 'Table'[Customer ID ]
            , 'Table'[Visit Date]
        )
        , ALL('Table'[Visit Date])
    )
    , 'Table'[Visit Date]
    ,
    , ASC
)

You just have to make sure, that the values of the column [Visit Date] can be ordered, for this reason I converted the column to the data type date.

Here is a little screenshot:

Hopefully, this provides what you are looking for.

 

Regards,

Tom

  • 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

     

     

    • TomMartens's avatar
      TomMartens
      Super User
      5 years ago

      Hey ctashwin ,

       

      please provide sample data, and explain what do you mean by "select the last 3 months". Do you select the the last 3 month inside a report by using a slicer or a filter?

       

      Also provide the expected result as in your initial post.

       

      Regards,

      Tom

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