Annual leave calendar

[Anonymous]

Hi Brains trust

I've been asked if it's possible in PowerBi to create a visual showing our staff annual leave and availability.

I've found a custom visualisation that I can use to show dates for events, but it doesn't seem to allow me to show a span of days. So if someone's leave starts on 02/02/2017 and ends on 08/02/2017 then I can easily show the start date. Or the end date. But not all dates inbetween.

Does anyone have any ideas on how I may be able to achieve this? We do have a development team who might be convinced to write a custom visualisation for me if that's an option...

Thanks all!

RossH

[Anonymous]

Hi RossH,

I am having a similar issue to you. Were you able to visualise this in a calander at all?

I am trying to give our Managers a visual in addition to their annual leave dashbaord that shows current leave booked for their team in a calander visual but really struggling. 

Any ideas?

[Phil_Seamark]

I take it you mean this custom visual?

https://app.powerbi.com/visuals/show/CalendarVisual1442774095659

While it won't accept a range, you can create ranges in your data.

So if I have 10 days of leave, (1st Jan to 10th Jan), rather than have 1 row for this event, you need to convert it to 10 rows for the event with each day represented (1st jan, 2nd Jan, 3rd Jan etc)

The visual will work for you then.

Post a sample of your data here and we can help you convert your single row to the multiple rows

[Anonymous]

Hi @Phil_Seamark 

I tried to open the link but it takes me to app source with all visuals listed there. What is the name of the visual you are referring to? If you please let me know, I shall search for the name instead.

[Anonymous]

Yes that's the visual I'm talking about. It'd be great if this functionality was built-in. I'll leave a comment 🙂

Oh ok. I see what you mean. Any help would be greatly apprecaited. Currenlty I'm just playing with an Excel spreadsheet with some dummy data in it that looks like this:

Date Start	Date End	Consultant Name
1/02/2016	5/02/2016	Ross
2/02/2016	10/02/2016	Carita
3/02/2016	3/02/2016	Mike
4/02/2016	8/02/2016	Fiona
13/04/2016	13/05/2016	Barks

[Phil_Seamark · Accepted solution]

Lets say the table you posted is called [Leave], and you also have a date table called [Dates] with a column called [Date], then this derived table can drive your visual.

Just use the "New Table" button on the Modeling tab of the ribbon in Power BI Desktop.

Leave Expanded = SELECTCOLUMNS(
                          FILTER(
    				 CROSSJOIN(Leave,Dates) ,
	           		'Leave'[Date Start] <= 'Dates'[Date]
                                && 'Leave'[Date End] >= 'Dates'[Date]
                            ),
                            "Consultant Name" , 'Leave'[Consultant Name],
                            "Is On Leave" ,'Dates'[Date]
                            )

[heygowtam]

@Phil_Seamark 

I Have table (Leave Table ) that shows employer leave details in Dates FROM  and TO, for this i need to build Measure

If worker is planning to go for leave for 5 Days ===> Planned Leave = 5, 

if worker is on leave its been 3 days and coming back after 2 days ======> Taken Leave = 3 and Planned Leave =2, 

If Worker has finished is leave then =======> Taken Leave = 5 

So, I need 2 columns in Table planned Leave and Taken Leave basde on Current Date