Create calendar of events based on start and end dates

[jsbourni]

Hi,

I'm trying to use a single event data table and globally retrieve the number of occurrences of each classroom per week/month. I think that getting a calendar out of the table would be great as I could drill through weeks and months.

The example below shows that classes may have multiple occurrences per day and per week.

The table is linked to a unique classroom table and both dates are linked to a calendar. With DATEDIFF I can get the number of weeks/months between the dates, but I don't get to see how each class distribute over time. 

Thanks!

start_date	end_date	hre_deb_act	hre_fin_act	no_local	start_day	end_day
#######	#######	13:00	15:45	V2-1430	Wed	Wed
#######	#######	13:00	15:45	V2-1430	Fri	Fri
#######	#######	08:30	11:30	V2-1430	Wed	Wed
#######	#######	08:30	11:30	V2-1430	Wed	Wed
#######	#######	08:30	11:30	V2-1430	Tue	Tue
#######	#######	08:30	11:30	V2-1430	Wed	Wed

[v-yangliu-msft]

Hi  @jsbourni ,

Thanks for the reply from @Greg_Deckler  and @Uzi2019 , please allow me to provide another insight:

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
var _table=
CALENDAR(
    DATE(2024,1,1),
    DATE(2024,6,1))
return
ADDCOLUMNS(
    _table,"Weekday",FORMAT([Date],"ddd"))

2. Create measure.

Measure =
var _selectfaculty=SELECTEDVALUE('Table'[Faculty])
var _selectclass=SELECTEDVALUE('Table'[classroom numbers])
return
COUNTX(
    FILTER(ALL('Table'),
    'Table'[Faculty]=_selectfaculty&&'Table'[classroom numbers]=_selectclass&&
    FORMAT('Table'[Start_date],"ddd")=MAX('Table 2'[Weekday])),[no_local])

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jsbourni]

Hi @v-yangliu-msft,

This is quite close but I'm not looking to distribute over weekdays, but over the calendar. I would like the data aggregate over dates and be able to slice the data with Department and classrooms. For example, if the course is given every Tuesday, for that specific class I would see an occurrence every Tuesday on the X axis between the dates specified. If the classroom is used a few times a week, I would like to count/sum the occurrences per date.

Thanks

[Greg_Deckler]

@jsbourni You want this it sounds like: Open Tickets - Microsoft Fabric Community

[jsbourni]

Hi @Greg_Deckler,

Thanks for the quick response. The formula you suggested is fairly close, but not quite what I am looking for. Let me formulate this in another way. In the table I have the classroom V2-1430 that is used EACH Wednesday between the start and end dates. Since this classroom is used many times a day, is there a way get this through a calendar that would show that each Wednesday that classroom was occupied once (or twice, or more)? I would use a line chart with dates on X axis and the frequencies (SUM) on the Y axis. I would use the Faculty as well as the classroom numbers as slicers in the page.

Thanks

[Uzi2019]

Hi @jsbourni 

If you want to create Calendar Table just simply take new table from modeling tab
Calendar= CALENDAR( MIN(start date),MAX(End date))

your Calendar is created with Start and end date.

I hope I answered your question!

[jsbourni]

Hi @Uzi2019,

Thank you for your suggestion. My need is more specific as answered to Greg. I need to place every occurrence of a course between the two dates. We have to assume that the class is given every week the same day at the same period (otherwise this would generate a separate entry). I would like to sum entries by classroom over the interval between dates.

Thanks