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

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

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

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.