Creating Upcoming Events Calendar

[lauravd]

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 date, so I need to create a calendar view. 

I have a table:

As my events need to happen every three weeks from start date, so I created a calendar table as per picture below.

My report has to look like picture below:

Any Ideas how to link these two tables?

Thanks in advance.

[Anonymous]

Hi @lauravd,

I'd like to suggest you take a look at below link to know how to expand detail date range form start date and end date.

Convert date ranges into list of dates?

After above steps, you can use detail date to link calendar table.

Regards,

Xiaoxin Sheng

[lauravd]

Hi, thank you for your advive. I still can't think of how to calculate this every 3 weeks bit..

[lauravd]

So I've created the calendar, but I cant figure out how to have this 'Event is due' visible only every 3 weeks in this calendar view.  I was thinking to use If statement, but it just doesnt do the job.4

Col = if( DATEADD('Events2'[start_date],21,DAY),"Event is Due",Blank())

Any ideas anyone?

[Anonymous · Accepted solution]

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

[lauravd]

Thank you.