Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
ctashwin
Frequent Visitor

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

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

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:

Annotation 2020-09-03 162406.png

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!

View solution in original post

7 REPLIES 7
v-kelly-msft
Community Support
Community Support

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:

Annotation 2020-09-03 162406.png

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
Super User
Super User

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:

TomMartens_0-1598936736035.png

Hopefully, this provides what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

 

 

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

@ctashwin - Try:

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

@ctashwin , try a new column like

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.