Track Email Workload in a historical table

[albrojo]

Hello PowerBI Community,

I hope you're all doing well. I am currently working on a project where we are trying to create a dashboard to track the workload of various departments based on incoming and unread emails. We have connected PowerBI to our Outlook mailboxes using "Microsoft Exchange Online," and we have separate tables for each department containing email data.

The Problem: The challenge we're facing is that every time we refresh the dashboard, we get a snapshot of the current state of emails. However, we are unable to save the number of unread emails to maintain a historical record.

What We Want: We want to create a table that stores the following variables for each department: date, department name, and the number of unread emails.

We have experimented with several approaches but have not yet found a way to automatically append a new row for each department with the current count of unread emails whenever the dashboard updates.

Any guidance or suggestions on how to achieve this in PowerBI would be greatly appreciated.

Thank you in advance for your help!

Best regards, 
Alberto

[Anonymous]

Hi Alberto,

Do i understand correctly that you want Power BI to add new data to tables when you are refreshing the data?

[albrojo]

Hi WilBr, not exactly. What I really want is to save in one table a daily summary of another table that is updated everyday. It is probably better understood with an example:

I have a table "EMAIL" that everyday updates to show the status of the mailbox, with fields: Subject, Send to, Send by, Read (Y/N). This table will be updated everyday at 00:00. What I want is that everyday when "EMAIL" is updated, a new row will be created in another table called "DAILY WORKLOAD". The new row will contain the date, and a count of the number of Read = "Y" from the "EMAIL" table.

[amitchandak]

@albrojo , refer if DAx append method can help

https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-...
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/