cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Repeat Users within Rolling 12 Month Period

Hey guys,

I need some help coming up with a calculated column. I have two tables...

a calendar table (has contiguous dates):

Date              Exercise

 1/1/2020 1/2/2020 1/3/2020 1/4/2020 FY20 Exercise 1 1/5/2020 … 3/1/2020 FY20 Exercise 2 … 12/31/2020 1/1/2021 1/2/2021 FY21 Exercise 1 …

and a fact table (does not have contiguous dates):

Exercise                Date           PersonID

 FY20 Exercise 1 1/4/2020 1 FY20 Exercise 2 3/1/2020 1 FY20 Exercise 2 3/1/2020 2 FY19 Exercise 2 3/1/2019 3 FY21 Exercise 1 1/2/2021 3

these are related on the Exercise column.

What I need to do is create a calculated column in my fact table indicating via a flag if a PersonID showed up at least twice in exercises within a rolling 12 month period. The end date of the twelve month period would be the date of the most recent exercise they participated in. The start date of the period would be the most recent exercise in which they participated that is within 12 months prior of the end date exercise.

so for example PersonID 1 participated in two exercises within a 12 month period. PersonID 2 only participated in one.

Person ID 3 participated in two exercises, BUT NOT in a rolling 12 month period.

Output would look like:

Exercise                Date        PersonID  IsRepeat

 FY20 Exercise 1 1/4/2020 1 1 FY20 Exercise 2 3/1/2020 1 1 FY20 Exercise 2 3/1/2020 2 0 FY19 Exercise 2 3/1/2019 3 0 FY21 Exercise 1 1/2/2021 3 0

1 ACCEPTED SOLUTION
Super User

try this calculated column formula

IsRepeat=VAR _id=FactTable[PersonID] VAR _tbl=FILTER(FactTable,FactTable[PersonID]=_id) VAR _max=MAXX(_tbl,FactTable[Date]) VAR _2ndMax=MAXX(FILTER(_tbl,FactTable[Date]<_max),FactTable[Date]) RETURN IF(_max<=EDATE(_2ndMax,12),1,0)

Super User

try this calculated column formula

IsRepeat=VAR _id=FactTable[PersonID] VAR _tbl=FILTER(FactTable,FactTable[PersonID]=_id) VAR _max=MAXX(_tbl,FactTable[Date]) VAR _2ndMax=MAXX(FILTER(_tbl,FactTable[Date]<_max),FactTable[Date]) RETURN IF(_max<=EDATE(_2ndMax,12),1,0)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors