Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lauravd
Frequent Visitor

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:
table 1.JPG

 

 

 

 

 

 

 

 

 

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

2.JPG

 

My report has to look like picture below:Requirement.JPG

 

 

 

 

Any Ideas how to link these two tables?

 

Thanks in advance.

 

1 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" )

13.PNG

 

Result:

14.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

5 REPLIES 5
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

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?

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" )

13.PNG

 

Result:

14.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thank you.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.