Custom Calendar by Akvelon

[Anonymous]

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?

[v-shex-msft]

HI @Anonymous ,

Power bi not support direct analysis date duration between two date fields, I'd like to suggest you create a bridge table with expand date range and link to original table.

Spread revenue across period based on start and end date, slice and dase this using different dates
Then you can use bridge table date as axis and original table amount as value to create visuals.

Regards,

Xiaoxin Sheng

[Anonymous]

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?

EMPLOYEE

Department

Absence reason

Fromdate

Untildate

workdays

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

[v-shex-msft]

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