Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@Pbiuserr , refer how to deal with start and end date
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |