Visitation Counter

[ctashwin]

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 Date	Visitaion Counter
1	12-Aug	1
1	13-Aug	2
2	14-Aug	1
1	15-Aug	3
2	16-Aug	2
3	17-Aug	1

Let me if there is a way to Acheive this,

Thanks in Advance for the help.

Regards,

Ashwin

[v-kelly-msft · Accepted solution]

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]

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]

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]

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]

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

[Greg_Deckler]

@ctashwin - Try:

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

[amitchandak]

@ctashwin , try a new column like

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