cancel
Showing results for
Did you mean:
Helper I

## Loop Through Each Row of a Table to Determine If They Showed Up Again

I'm trying to come up with something to track the amount (or percentage) of "recurring" customers.

What I want to do, is check through a table of reservations that occured, and determine

1) How many unique customers had reservations

2) How many of these customers, also had a reservation in the 90 days prior to their start date.

Example:

I know how to get the count of the 8 distinct customers in the list shown above, but I'm stumped on how to loop through the table again to see if they had a different reservation no more than 90 days prior.

1 ACCEPTED SOLUTION
Super User

@MPetramalo214 Maybe:

``````Measure =
VAR __Table =
SUMMARIZE(
'Table',
[Owner Id],
"__LastDate",MAX([Check-In Date])
),
"__PreviousDate",MAXX(FILTER(ALL('Table'),[Check-In Date]<[__LastDate] && [Owner Id]=EARLIER([Owner Id]),[Check-In Date])
),
"__Diff",([__LastDate] - [__PreviousDate]) * 1.
)
RETURN
COUNTROWS(FILTER(__Table,[__Diff]<90))``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition
Super User

@MPetramalo214 Maybe:

``````Measure =
VAR __Table =
SUMMARIZE(
'Table',
[Owner Id],
"__LastDate",MAX([Check-In Date])
),
"__PreviousDate",MAXX(FILTER(ALL('Table'),[Check-In Date]<[__LastDate] && [Owner Id]=EARLIER([Owner Id]),[Check-In Date])
),
"__Diff",([__LastDate] - [__PreviousDate]) * 1.
)
RETURN
COUNTROWS(FILTER(__Table,[__Diff]<90))``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.