Forum Discussion

GopVan's avatar
GopVan
Frequent Visitor
3 years ago

Repeat Values between Start Date and End Date

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 NameEvent HeadDepartmentStart DateEnd 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?