I have 2 tables: Calendar and Events
Calendar table was generate as below:
It contains dates between 2022 and 2030.
Source = List.Dates(start_date, Duration.Days(end_date-start_date)+1, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
#"Month" = Table.AddColumn(#"Year", "Month", each Date.Month([Date]), Int64.Type),
#"MonthName" = Table.AddColumn(#"Month", "MonthName", each Date.MonthName([Date]), type text),
#"Quarter" = Table.AddColumn(#"MonthName", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"WeekOfYear" = Table.AddColumn(#"Quarter", "WeekOfYear", each Date.WeekOfYear([Date]), Int64.Type)
Events
| Event Name | Event Head | Department | Start Date | End Date |
| E1 | CIO | IT | 01/02/2023 | 28/02/2023 |
| E2 | COO | Operations | 01/04/2023 | 30/04/2023 |
| E3 | COO | Operations | 01/06/2023 | 30/06/2023 |
| E4 | MGR | Operations | 01/06/2023 | 30/06/2023 |
Dates are in mm/dd/yyyy format. And only 1 year (say 2023) will be visualized at a time.
Relationship: The Start Date of the Events table(Many) and the Date of the Calendar table(One) has a Many-to-One relationship. It could even be the End date.
Visual: I would like to visualize the above in a matrix and show an asterisk (or any symbol) for ALL WEEKS between the Start Date and End Date.
Column Headers of the Matrix are: Year, Month, Week of Year
Row Headers of the Matrix are: Event Head, Event Name
Problem:
If I add Event Name to the values, it appears only on the Start Week. E2 and E3 are the same event but happen at 2 different times. E2 is shown in April's first week and E3 in June's first week. But since the Start and End Date covers all the weeks of the month, I want all weeks between the dates to be highlighted, not just the first week.
So, I used the following Measure. It returned an asterisk for all weeks between the Start and End date but for E2 & E3 June weeks were not shown although April turned out the way I expected. This measure did not work when the same Event happens more than once a year. Only the first occurrence was shown.
MeasureEvent =
VAR Startdt = CALCULATE(MIN(Events[Start date]), REMOVEFILTERS('Calendar'))
VAR Enddt = CALCULATE(MIN(Events[End date]), REMOVEFILTERS('Calendar'))
VAR EventPeriod = MIN('Calendar'[Date])>=Startdt && MIN('Calendar'[Date])<= Enddt
VAR Result = IF(EventPeriod, "*")
Return Result
How can I show the Events (happening more than once a year) by highlighting all weeks between the Start and End Date?
Can a Measure be computed to do this or should I duplicate Events table data for every week to achieve this?
