Sum values matrix based on opening times.

[maartjedutchy]

 Hello Power BI community!

I am new to Power BI and loving it, but struggling with the following:
I have created a heatmap based on time vs days in the week and the total number of incoming posts. So basically shows incoming posts per hour per day. Now I need one of the two heatmaps to calculate the incoming posts based on opening times of the customer care team.. So if they started at 08:00AM all posts that came in from 00:00-08:00 (and the previous day after closing time) need to sum on the 08:00 value, so you can show backlogs.

How do I calculate the sum of incoming posts based on various opening times during the week?

So I need to end up with something like this:

Hope you can help me out!

Thanks a million,

Maartje

[v-qiuyu-msft · Accepted solution]

---

Now I need one of the two heatmaps to calculate the incoming posts based on opening times of the customer care team.. So if they started at 08:00AM all posts that came in from 00:00-08:00 (and the previous day after closing time) need to sum on the 08:00 value, so you can show backlogs. 

---

Hi @maartjedutchy,

From your description, there should have opening time and closing time columns in your source table? Can you share some dummy data in your table, and expected results you want to display in the matrix visual?

Best Regards,
Qiuyun Yu

[maartjedutchy]

Dear @v-qiuyu-msft,

Thanks for your reply!

I haven't integrate opening times in my source data yet. Maybe I should create a seperate datafile with opening times and use Vlookup or relationships to link this the my main source. If you have any tips for this, please let me know.

My data looks like this:
Publish date column(dd/mm/yyyy hh:mm format) | Count = 1 column | Time column (copied Publish date, formatted to start time)

I have a seperate source file with days of the week and weekday nummers.

Currently matrix is days of the week column (relationship with publish date) vs Time column with value Count of Count.

The expected result is shown in my originel post. I want i.e. values related to times 00:00-08:00 to sum in the 08:00 value in the matrix. 

Thanks!

Maartje

[v-qiuyu-msft]

---

@maartjedutchy wrote:

My data looks like this:
Publish date column(dd/mm/yyyy hh:mm format) | Count = 1 column | Time column (copied Publish date, formatted to start time)

I have a seperate source file with days of the week and weekday nummers.

Currently matrix is days of the week column (relationship with publish date) vs Time column with value Count of Count.

The expected result is shown in my originel post. I want i.e. values related to times 00:00-08:00 to sum in the 08:00 value in the matrix. 

---

Hi @maartjedutchy,

Can you share some dummy data of those two tables you mentioned? What do you mean "values related to times 00:00-08:00 to sum in the 08:00 value in the matrix. "? Sum 00:00-08:00 values for each weekday and place in 08:00 cell? It would be better if you can tell the results based on provided dummy data.

Best Regards,
QiuyunYu