Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Automated emails from table values

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 invo...
General Comment
Need Help
Tips and Tricks
  • DataZoe's avatar
    DataZoe
    5 years ago

    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's avatar
    Anonymous
    5 years ago

    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 ğŸ˜€
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
DataZoe
Microsoft Employee
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      DataZoe
      Microsoft Employee
      5 years ago

      Anonymous that is awesome to hear!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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