Forum Discussion
Solved
Creating Upcoming Events Calendar
Hi, I need some help creating a calendar with upcoming events date calculation. I am new to Power BI, so just can't get my head around it. An Event needs to happen every three weeks from start...
HI lauravd,
You can use below formula to create calendar table and add event tag.
Table formula:
Table = VAR _calendar = CALENDAR ( MIN ( Test[Start Date] ), MAX ( Test[End Date] ) ) RETURN SELECTCOLUMNS ( FILTER ( CROSSJOIN ( Test, _calendar ), [Start Date] <= [Date] && [End Date] >= [Date] ), "ID", [ID], "Data", [Date] )Calculate column:
Event Tag = VAR _count = COUNTROWS ( FILTER ( ADDCOLUMNS ( 'Table', "WeekDay", WEEKDAY ( [Data], 2 ) ), [WeekDay] = 1 && [Data] <= EARLIER ( 'Table'[Data] ) && [ID] = EARLIER ( 'Table'[ID] ) ) ) RETURN IF ( MOD ( _count - 1, 3 ) = 0 && WEEKDAY ( [Data], 2 ) = 1, "Y" )Result:
Regards,
Xiaoxin Sheng
HI lauravd,
You can use below formula to create calendar table and add event tag.
Table formula:
Table =
VAR _calendar =
CALENDAR ( MIN ( Test[Start Date] ), MAX ( Test[End Date] ) )
RETURN
SELECTCOLUMNS (
FILTER (
CROSSJOIN ( Test, _calendar ),
[Start Date] <= [Date]
&& [End Date] >= [Date]
),
"ID", [ID],
"Data", [Date]
)
Calculate column:
Event Tag =
VAR _count =
COUNTROWS (
FILTER (
ADDCOLUMNS ( 'Table', "WeekDay", WEEKDAY ( [Data], 2 ) ),
[WeekDay] = 1
&& [Data] <= EARLIER ( 'Table'[Data] )
&& [ID] = EARLIER ( 'Table'[ID] )
)
)
RETURN
IF ( MOD ( _count - 1, 3 ) = 0 && WEEKDAY ( [Data], 2 ) = 1, "Y" )
Result:
Regards,
Xiaoxin Sheng
Thank you.