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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pbiuserr
Post Prodigy
Post Prodigy

Data model in order to achieve rolling periods

Hello, 
I have main table with "start date" and "end date", created a master calendar table and did two inactive one to many connections (one from calendar[date] to fct_table[start date], second from calendar[date] to fct_table[end date]) and now I'd like to create a measure to do rolling 13 month from slicer selection. I know the measure very well

 

Rolling 13 month = CALCULATE ( [Measure], DATESINPERIOD(calendar[date], max(calendar[date]), -13, MONTH) )

I've copied master calendar and did same connection from Calendar Rolling to fct_table, building a chart from Year-Month column of Callendar Rolling, taking Rolling 13 month measure and the slicer is from master calendar year-month column. When I slice, I have no -13 months in chart. When I play with calendar rolling connections, I got all entries

How to manage that, to let this simple measure works properly? I'd ideally avoid active connections between master calendar and fct_table as other measures could be damaged, I simply used USERELATIONSHIP in there

2 REPLIES 2
amitchandak
Super User
Super User

@Pbiuserr , refer how to deal with start and end date

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

How to take rolling of snapshot (between start and end)

 

Average of Rolling, Average of Snapshots: https://youtu.be/_pZRdLAJxxA

Hi @amitchandak 

I know the theory and I use no active relationships, but how to build rolling calendar and connect / not connect it to let this measure with DATESINPERIOD works properly and shows the interval as in measure?

 

Lets say, active employees by year-month -13 months from slicer selection in the scenario with master calendar with inactive relationships between calendar[date] and facttable[startdate] / facttable[enddate] and be displayed like that in line chart?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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