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...
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

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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

    • lauravd's avatar
      lauravd
      Frequent Visitor
      8 years ago

      Thank you.