Skip to main content
cancel
Showing results for 
Search instead 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

Reply
StephenK
Resolver I
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/2020FY20 Exercise 1
1/5/2020 
 
3/1/2020FY20 Exercise 2
 
12/31/2020 
1/1/2021 
1/2/2021FY21 Exercise 1
 

 

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

 

Exercise                Date           PersonID

FY20 Exercise 11/4/2020    1
FY20 Exercise 23/1/2020    1
FY20 Exercise 23/1/2020    2
FY19 Exercise 23/1/2019    3
FY21 Exercise 11/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 11/4/2020    1    1
FY20 Exercise 23/1/2020    1    1
FY20 Exercise 23/1/2020    2    0
FY19 Exercise 23/1/2019    3    0
FY21 Exercise 11/2/2021    3    0

 

 

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
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)

View solution in original post

1 REPLY 1
wdx223_Daniel
Super User
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)

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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