Forum Discussion
Custom Calendar by Akvelon
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
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
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