Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Custom Calendar by Akvelon

Hi,

 

I'm trying to create a visual with custom calendar akvelon. I'm relatively new to PowerBI. I've connected a basic excel source with annual leave data. There are three main columns, which I'm trying to use(intially) are "from date", "to date" and "employee".

 

I've also created a new date table to match the ranges.

 

The aim is to display a user or users and their annual leave for the year. I cannot get the duration between "from date" and "to date". Any ideas/help would be welcome?

Need Help

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Xiaoxin Sheng,

       

      I have a custom table created already for dates, I just have one query, before using the dax statement to link the tables. Do I need create a a unique identifier to link both tables?

       

      Currently on my employee table i have the below list and on the date table, I have one date column.

       

      Do I need a custom column on both tables to link the tables, as per the below dax script?

       

      EMPLOYEEDepartmentAbsence reasonFromdateUntildateworkdays

       

      booking date range = 
VAR _calendar =
    CALENDAR ( MIN ( booking[dte_start] ), MAX ( booking[dte_finish] ) )
RETURN
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN ( booking, _calendar ),
            booking[dte_start] <= [Date]
                && booking[dte_finish] >= [Date]
        ),
        "uid_booking", [uid_booking],
        "Date", [Date]
    )

       Kind Regards,

      James

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Anonymous ,

        Actually, you need to use unique fields and date fields to create that bridge table. (notice: unique field is used to link those tables)
        According to your description, employee fields should be the relationship key of your tables.

        Regards,

        Xiaoxin Sheng