Create Date related Alert from a Table

[FPagden]

Hi,

I'm a beginner user. 

My organisation has various mandated annual tests all employees must undertake and pass, with varying time periods for which that test lasts (mostly 6 or 12 months).

I want to set up an alert to warn managers when an employee in their team is due to run out soon (eg next 3 months) so they can arrange a test, and and alert when they've run out. 

Clearly this would be based on today's date vs the date their test pass runs out.

I don't think this would work with a KPI or a gauge as it needs to apply to all 500 employees in the organisation - I've built a page in the report witha  very simple table, but all the instructional content online seems to refer to KPIs etc.

Can anyone help? Sorry that this is a super simple question!

[MAwwad · Accepted solution]

you can use a calculated column in your PowerBI table. Here's an example:

1. Add a calculated column to your table, named "Test Expiration Alert".
2. In the formula bar, enter the following expression:
   
   

   = IF(TODAY() > [Test Expiration Date] + 90, "Test Expired", IF(TODAY() > [Test Expiration Date] - 90, "Test Expiring Soon", ""))

   This formula compares today's date with the test expiration date and returns "Test Expiring Soon" if the test will expire in the next 90 days and "Test Expired" if it has already expired. If neither of these conditions are met, the result will be an empty string.

   1. Use the "Test Expiration Alert" calculated column in a matrix or table visualization to show the status of each employee's test expiration.
   2. Set up row-level security so that each manager only sees their own team's data.

      You can also create a measure to count the number of expiring or expired tests for each team and display it in a card visualization.

      This is a simple solution that can be easily adapted to your needs. I hope it helps!

[FPagden]

Thank you for your help!