Forum Discussion

lauravd's avatar
lauravd
Frequent Visitor
8 years ago
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 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.

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      lauravd
      Frequent Visitor
      8 years ago

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

      • lauravd's avatar
        lauravd
        Frequent Visitor
        8 years ago

        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?