Hi Data Expert Team,
There is one urgent client requirement to build a Power BI report to visualize the organization’s internal task list.
So in team’s calendar shows the meeting for each day and time, a little similar visual client is looking for.
So are there any possibilities to achieve or visual available for Power BI
Please find the attached image
Your help is highly appreciated
Best Regards
Pijush
Hi @PijushRoy. Deneb could produce such a matrix quite easily, but likely would have rudimentary (compared with Teams calendar) interactivity. I’ll make up a small dataset and give it a whirl, but please state all filter and interactivity requirements. Also, is this a calendar for one person/team or many? If many, how are they to be presented? One at a time? More details please.
Greg
Hi @Greg
Very thanks for looking into the topic and prompt reply.
The report is for one person (if required, multiple reports for different person)
Kindly provide one sample with Deneb, it will greatly help
Thanks a lots
Regards
Pijush
Hi @PijushRoy
Here’s a first rough draft of a weekly calendar using a small, made-up dataset and Deneb/Vega-Lite:
This represents about 3 hours work (for myself, an accomplished Deneb/Vega-Lite developer), so a full solution would be a not-insignificant development effort. Some of the items/features that should be developed in the future include:
Before going any further, it would be a good idea to have you say if we’re heading in the right direction at all. If so, in order to develop a more complete solution, I’d need:
If I am on the right track and you’d like further development, please say so and provide a sample dataset and requirements. (I’ve attached my small made-up dataset for your reference.) It’s the weekend and I have other commitments, but will try to generate a 2nd version later today.
Hope it helps.
Greg
Tasks.xlsx (9.4 KB)
Thank You so much @Greg and the developer team
That is an incredible effort.
The task is almost the same for all weeks but it will include Saturday and Sunday as well. If a task is for 30 minutes or 15 minutes, if we can highlight this portion in place of a full hour, it will be awesome.
I have completed the Deneb course and looking into the other Deneb events that are available on the Enterprise DNA website.
The course is very very helpful. Thanks @Greg
I am excited and waiting for the next version.
If you please share JSON template, I can look and learn the process.
Thank you very much for your prompt support
Best Regards
Pijush
Hi @PijushRoy
OK, here’s V2 with 1/4 and 1/2 hour displays and using the same colouring for weekends and weekdays:
This draft is essentially just:
Here’s the JSON specification code:
{
"title": {
"anchor": "start",
"align": "left",
"offset": -10,
"text": "eDNA Forum - Deneb Calendar - V2",
"font": "Verdana",
"fontSize": 36,
"fontWeight": "bold",
"fontStyle": "normal"
},
"data": {"name": "dataset"},
"transform": [
{
"calculate": "month(datum['DateTime'])",
"as": "_month"
},
{
"calculate": "monthFormat(datum['_month'])",
"as": "_month_name"
},
{
"calculate": "day(datum['DateTime'])",
"as": "_day_of_week"
},
{
"calculate": "timeFormat(datum['DateTime'], '%d')",
"as": "_day_of_month"
},
{
"calculate": "dayFormat(datum['_day_of_week'])",
"as": "_day_name"
},
{
"calculate": "datum['_day_name'] + '|' + datum['_day_of_month']",
"as": "_day_label"
},
{
"calculate": "hours(datum['DateTime'])",
"as": "_hour"
},
{
"calculate": "timeFormat(datum['DateTime'], '%I')",
"as": "_hour_padded"
},
{
"calculate": "timeFormat(datum['DateTime'], '%-I:%M %p')",
"as": "_hour_name"
},
{
"calculate": "datum['_hour_padded'] + '_' + datum['_hour_name']",
"as": "_hour_label"
},
{
"filter": {
"field": "_hour",
"range": [6, 18]
}
}
],
"params": [
{
"name": "_month_colour",
"value": "black"
},
{
"name": "_weekend_colour",
"value": "#E3E3E3"
},
{
"name": "_weekday_colour",
"value": "#E3E3E3"
},
{
"name": "_task_colour",
"value": "#35558A"
},
{
"name": "_text_colour",
"value": "white"
}
],
"vconcat": [
{
"name": "MONTH_TITLE",
"width": 180,
"height": 50,
"transform": [
{
"joinaggregate": [
{
"op": "min",
"field": "__row__",
"as": "_min_row_number"
}
]
},
{
"filter": "datum['__row__'] == datum['_min_row_number']"
}
],
"mark": {
"type": "text",
"color": {
"expr": "_month_colour"
},
"align": "left",
"fontSize": 24,
"fontWeight": "bold",
"x": 0,
"y": 40
},
"encoding": {
"text": {
"field": "_month_name",
"type": "nominal"
}
}
},
{
"name": "CALENDAR",
"spacing": 2,
"facet": {
"column": {
"field": "_day_label",
"sort": {
"field": "_day",
"type": "quantitative",
"order": "ascending"
},
"header": {
"lineBreak": "|",
"labelLineHeight": 16,
"labelAnchor": "start",
"labelExpr": "[slice(datum.value, 0, length(datum.value)-3), slice(datum.value, -2, 100)]",
"labelFontSize": 12,
"title": false
}
},
"row": {
"field": "_hour_label",
"sort": {
"field": "_hour",
"type": "quantitative",
"order": "ascending"
},
"header": {
"labelAngle": 0,
"labelAlign": "left",
"labelExpr": "slice(datum.value, 3, 100)",
"labelFontSize": 12,
"title": false
}
}
},
"spec": {
"layer": [
{
"name": "BACKGROUND",
"width": 160,
"height": 48,
"mark": {"type": "rect"},
"encoding": {
"color": {
"condition": [
{
"test": {
"field": "_day_of_week",
"oneOf": [0, 6]
},
"value": {
"expr": "_weekend_colour"
}
}
],
"value": {
"expr": "_weekday_colour"
}
}
}
},
{
"name": "FOREGROUND",
"width": 120,
"height": 48,
"transform": [
{
"filter": {
"field": "Task ID Measure",
"gt": 0
}
},
{
"calculate": "( datum['Task End DateTime'] - datum['Task Start DateTime'] ) / (60 * 60 * 1000 )",
"as": "_duration_in_hours"
}
],
"mark": {
"type": "rect",
"color": {
"expr": "_task_colour"
},
"stroke": "white"
},
"encoding": {
"y": {
"datum": 100,
"type": "quantitative",
"scale": {
"domain": [0, 100]
},
"axis": null
},
"y2": {"datum": { "expr": "datum['_duration_in_hours'] == 0.25 ? 75 : datum['_duration_in_hours'] == 0.50 ? 50: datum['_duration_in_hours'] == 0.75 ? 25 : 0" }}
}
},
{
"name": "FOREGROUND_TEXT",
"width": 180,
"height": 50,
"transform": [
{
"filter": {
"field": "Task ID Measure",
"gt": 0
}
},
{
"calculate": "timeFormat(datum['Task Start DateTime'], '%-I:%M %p')",
"as": "_start_hour_name"
},
{
"calculate": "timeFormat(datum['Task End DateTime'], '%-I:%M %p')",
"as": "_end_hour_name"
},
{
"calculate": "datum['_start_hour_name'] + '-' +datum['_end_hour_name'] + '|' + datum['Task Notes Measure']",
"as": "_multiline_task_label"
},
{
"calculate": "datum['Task Notes Measure']",
"as": "_task_label"
}
],
"mark": {
"type": "text",
"align": "left",
"lineBreak": "|",
"xOffset": -78,
"yOffset": -17,
"limit": 160,
"color": {
"expr": "_text_colour"
}
},
"encoding": {
"text": {
"field": "_task_label"
}
}
}
]
}
}
]
}
This is still rough, and further development would be warranted (for example, it doesn’t properly handle situations where there are multiple events in a single 1-hour interval), but should be a useful start.
Hope it helps.
Greg
eDNA Forum - Deneb Calendar - V2.pbix (1.5 MB)
Tasks.xlsx (9.5 KB)
Hi @Greg
I am writing to express my sincere gratitude for the exceptional development work and unwavering support you have provided.
I am looking into the solution, trying to learn.
Once again, thank you for your exceptional work and unwavering support.
Best Regards
Pijush
Hi @Greg
I am looking to modify a little to reflect if, within one hour, there are multiple tasks. Is it possible to show it in a single cell? There are many 15mins tasks available in the data.
Also if same time have an overlap task, how to handle (The chances are very low, but may be an overlap task). Not important but better to have functionality.
DatesWithTimes =
GENERATESERIES (DATE (2024, 01, 01), DATE (2024, 02, 29), 1/24)
If I update the measure and use 96 in place of 24, it will break the cell for 15-minute intervals.
Could you please guide me to achieve the above point?
Your support is highly required.
Thanks and Regards
Pijush
Hi @PijushRoy.
Please do not post to solved threads. Many forum members do not review solved threads, so it is in both the forum members’ and your best interest to create a new thread for each new question. (You can link to a solved thread if it’s pertinent to your new issue.)
@EnterpriseDNA, please split this thread into it’s own thread starting with the above post.
Regardless, not off the top of my head … this was one of the reasons why I asked for complete requirements, and, as none were provided, I started with a solution based on 1-hour intervals. If you facet by 1/4 hour you could do it, but it would make the visual 4x as busy. I’ll think on it a bit and see if I can think of anything.
Greg
