Forum Discussion
Calendar Visual
Hi All,
I am trying to create a calendar visual that shows the total number of employees on site on any given day.
Dataset 1 - All employees who should be available on any given day.
| Employee Name | Trade |
| Jone Jones | Scaffolder |
| Andy Blue | Fitter |
Dataset 2 - Sickness
| Employee Name | Start Date | End Date |
| Jone Jones | 01/01/2021 | 10/01/2021 |
| Andy Blue | 01/05/2021 | 10/05/2021 |
Dataset 3 - Holiday
| Employee Name | Start Date | End Date |
| Jone Jones | 11/01/2021 | 12/01/2021 |
| Andy Blue | 11/05/2021 | 12/05/2021 |
The logic behind this visual will consist of the following
- All employees in dataset 1 should be on site at any given day.
- If employees have dates in datasets 2 & 3, these will need to be subtracted from the given days in the calendar visual
For example, the calendar visual for January 2021 will show that Andy Blue is available every day but Jone Jones is not available between dates. 01/01/2021 - 12/01/2021
Any ideas on how this can be achieved?7
Many thanks,
Hi Anonymous ,
Here are the steps you can follow：
1. Create calculated column.
Column = IF(TODAY()>=[Start Date]&&TODAY()<=[End Date],1,0)2. Create measure.
vailable = var _noavailable=SUM(Merge[Column]) return COUNT(Dataset1[Employee Name])-_noavailable3. Use the card chart to place the measure in
4. Result:
Today is 2021.9.16. According to the conditions, neither of them is in working hours, so no one today
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
7 Replies
Hi Anonymous ,
Here are the steps you can follow：
1. Create calculated column.
Column = IF(TODAY()>=[Start Date]&&TODAY()<=[End Date],1,0)2. Create measure.
vailable = var _noavailable=SUM(Merge[Column]) return COUNT(Dataset1[Employee Name])-_noavailable3. Use the card chart to place the measure in
4. Result:
Today is 2021.9.16. According to the conditions, neither of them is in working hours, so no one today
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
what were you expecting to see? there are plenty of calendar type custom visualisations https://appsource.microsoft.com/en-us/marketplace/apps?product=power-bi-visuals&search=calendar&page=1
which may or may not work for you. It just depends on what your expectation is. You need to give a clearer idea of what how you are wanting to display this information.
Hi,
Thanks for your response. I have tried numerous of these calendar visuals but couldn't achieve the desired look.
Please find an example report visual attached.
Hope this helps.https://www.dropbox.com/scl/fi/xh5axxgbpy65rlnfr3x3c/Labour-Availability-Example.docx?dl=0&rlkey=n9eixh6n0atcqivn0pn0rh6y5
unfortunately if you can't find the correct visual in the custom options then it doesn't exist. You either need to develop your own visual which for most people isn't really realistic or think about adjusting your requirements in how you demonstrate this. There is however this which is charticulator which might be of use to you, i haven't used it but it looks like it might have potential.
https://www.burningsuit.co.uk/blog/2021/06/charticulator-in-power-bi-1/
Hi Anonymous ,
You can find Calendar by MAQ Software in Get more visual.
Here are the steps you can follow：
1. Create calculated table
Merge = UNION('Dataset 2 - Sickness','Dataset 3 - Holiday')2. Open Calendar by MAQ Software. Visual object, and place the columns of the Merge table below.
3. Result:
When Employee is available on a certain day, the color is not displayed. When Employee is not available on a certain day, it is displayed in the specified color.
This is the relevant information of Calendar by MAQ Software:
https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381844
https://www.c-sharpcorner.com/article/create-event-calendar-in-power-bi/
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hello,
We offer building custom visuals or enhancing existing visuals. Based on your asks and attached mock-up we will be able to build a custom visual for your needs.
If you have an urgent need for a custom feature or visual, please contact [email protected]
