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 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

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 ğŸ˜€

6 Replies

  • DataZoe's avatar
    DataZoe
    Microsoft Employee
    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
      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.