Automated emails from table values

[Anonymous]

i currently have a table that counts down the number of days from 180  to 0 to remind me when an invoice is due

does anyone know if there is a way to create an email alert to tell me when an invoice hits 0 on the countdown?

Is it possible to create a filter table where it only adds a new row of data when an invoice hits zero and tie this to a card counts the number of rows. when the row increases by 1 then this can trigger an automated email alert?

any help would be greatly appreciated

[DataZoe · Accepted solution]

@Anonymous If you pin your card with # invoices at 0 to a dashboard you can set up an alert following this doc Set data alerts in the Power BI service - Power BI | Microsoft Docs.

For your ask on the daily change alert, you can create a measure that populates a card that will only be the daily change and pin that to a dashboard and have the alert set up.  For example:

Today Change Since Yesterday 0 Invoices =
VAR yesterdayinv =
CALCULATE (
COUNTROWS ( 'Invoices' ),
FILTER ( 'Invoices', [Days Until Due] <= 0 ),
'Date'[Date]
= TODAY () - 1
)
VAR todayinv =
CALCULATE (
COUNTROWS ( 'Invoices' ),
FILTER ( 'Invoices', [Days Until Due] <= 0 ),
'Date'[Date] = TODAY ()
)
VAR diff = todayinv - yesterdayinv
RETURN
diff

[DataZoe · Accepted solution]

@Anonymous If you pin your card with # invoices at 0 to a dashboard you can set up an alert following this doc Set data alerts in the Power BI service - Power BI | Microsoft Docs.

For your ask on the daily change alert, you can create a measure that populates a card that will only be the daily change and pin that to a dashboard and have the alert set up.  For example:

Today Change Since Yesterday 0 Invoices =
VAR yesterdayinv =
CALCULATE (
COUNTROWS ( 'Invoices' ),
FILTER ( 'Invoices', [Days Until Due] <= 0 ),
'Date'[Date]
= TODAY () - 1
)
VAR todayinv =
CALCULATE (
COUNTROWS ( 'Invoices' ),
FILTER ( 'Invoices', [Days Until Due] <= 0 ),
'Date'[Date] = TODAY ()
)
VAR diff = todayinv - yesterdayinv
RETURN
diff

[Anonymous]

i have created a date table to filter data from my main table using

Filtered invoice Reminder Table = SELECTCOLUMNS(FILTER('Invoice Reminder', [Days Till Next Invoice] > -7),

"Workflow ID", 'Invoice Reminder'[Project Site Governance Workflow Id],

"Study Title", 'Invoice Reminder'[Project Title],

"Amount Raised", 'Invoice Reminder'[Amount Raised 2],

"Date Ledger Raised", 'Invoice Reminder'[Ledger Raised 2].[Date])

and added your measure according to my data as below

Today Change Since Yesterday 0 Invoices 2 =

VAR yesterdayinv =

CALCULATE (

COUNTROWS ( 'Filtered invoice Reminder Table' ),

FILTER ( 'Filtered invoice Reminder Table','Filtered invoice Reminder Table'[Next Invoice Date].[Date] - TODAY() <= -2 ),

'Invoice Reminder'[Next Due Invoice]

= TODAY () - 1

)

VAR todayinv =

CALCULATE (

COUNTROWS ( 'Filtered invoice Reminder Table' ),

FILTER ( 'Filtered invoice Reminder Table', 'Filtered invoice Reminder Table'[Next Invoice Date].[Date]- TODAY() <= -2 ),

'Invoice Reminder'[Next Due Invoice] = TODAY ()

)

VAR diff = todayinv - yesterdayinv

RETURN

diff

i have 2 invoices hitting minus 2 days sp hopefully this will register a hit on the card and this can then be put through flow or something similar i am assuming?

[DataZoe]

@Anonymous Once you pin the card to a dashboard, you can click the "..." on the tile and set up the alert. No flow needed :).

You can also set them up with a flow too, if you don't want to create a dashboard.

[Anonymous · Accepted solution]

@DataZoe i have modified the measure you created as below

Automated Email Alert =

VAR yesterdayinv =

CALCULATE (

DISTINCTCOUNT('Filtered invoice Reminder Table'[Study Title]),

FILTER ( 'Filtered invoice Reminder Table', [Days Till Next Invoice 2] = -2 ),

'Filtered invoice Reminder Table'[Next Invoice Date]

= TODAY ()

)

VAR todayinv =

CALCULATE (

DISTINCTCOUNT('Filtered invoice Reminder Table'[Study Title]),

FILTER ( 'Filtered invoice Reminder Table', [Days Till Next Invoice 2] = -2 ),

'Filtered invoice Reminder Table'[Next Invoice Date] = TODAY () +2

)

VAR diff = todayinv - yesterdayinv

RETURN

diff

i now records correctly and when added to flow as an alert works as inteneded

thank you again for your help

it is very much appreciated 😀

[DataZoe]

@Anonymous that is awesome to hear!

[Anonymous]

i have tested the above and the card still reads as zero even though 2 invoices are now due according to the measure. these are present in my filtered table

i have looked at the table and although i have only 2 invoices showing as due in the data section i can see each invoice has multiple rows, would this require a distinct count filter? i notice the readout at the bottom reads 36 rows disctinct count 2