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 counter to reset to 1 when i change the add new data and drop off older data. Eg, Drop off the earliet week and Add 1 new week of data.

 

 

Customer ID Visit DateVisitaion Counter
112-Aug1
113-Aug2
214-Aug1
115-Aug3
216-Aug2
317-Aug1

 

Let me if there is a way to Acheive this,

 

Thanks in Advance for the help.

 

Regards,

Ashwin

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

7 Replies

  • 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])))
  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    ctashwin , try a new column like

    countx(filter(Table, [Customer ID] = earlier([Customer ID]) && [Visit Date] = earlier([Visit Date])),[Visit Date] )

  • 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

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    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!