cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Annual leave calendar

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

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

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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
Microsoft
Microsoft

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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
Not applicable

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 StartDate EndConsultant Name
1/02/20165/02/2016Ross
2/02/201610/02/2016Carita
3/02/20163/02/2016Mike
4/02/20168/02/2016Fiona
13/04/201613/05/2016Barks

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

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.