Forum Discussion

mayurbajaj's avatar
mayurbajaj
New Member
2 years ago

Need Help with Virtual Tables

Hi ALl,

I got two tables, one of which had some invoices for an account with multiple start dates and end dates (Table Bill) and another table which has the bill frequency (Table Account).

I want to find out if any account has not received any invoices in time or not.

As an example, 

Max Invoice Start Date: 01/03/2024

Max Invoice End Date: 31/03/2024

Account Billing Frequency: Monthly (there are multiple valies here)

Final Output: If the account is set up as Monthly billing frequency i want to add 45 days into the Max End Date giving me an expected date of 15/05/2024 by which i should have received the invoice for April Period. After 15/05/2024, it should start to get reported and by how many days it's late.

 

I want to be able to do this using virtual tables within a DAX Measure rather than creating seperate tables for this.

 

Any suggestions.

 

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi mayurbajaj ,

     

    Please provide sample data in table format and show your expected results.

     

    Best regards,
    Community Support Team_ Scott Chang

    • mayurbajaj's avatar
      mayurbajaj
      New Member
      2 years ago

      These are my 2 tables:

      So far i ave tried this

      LateInvoiceDays =
      VAR MaxEndDate = MAX('Bill Header'[Bill End Date])
      VAR MaxStartDate = MAX('Bill Header'[Bill Start Date])
      VAR BillingFrequency = MAX('Account'[Bill Cycle])  // Assuming BillingFrequency is a single value for each account

      // Calculate expected invoice date based on billing frequency
      VAR ExpectedInvoiceDate =
          SWITCH (
              BillingFrequency,
              "Monthly", EDATE(MaxEndDate, 45),  // Add 45 days for monthly billing frequency
              "Quarterly", EDATE(MaxEndDate, 105),  // Add 105 days for quarterly billing frequency
              "Bi-Monthly", EDATE(MaxEndDate, 75),  // Add 75 days for quarterly billing frequency
              // Add more cases for other billing frequencies if needed
              BLANK()  // Return BLANK if BillingFrequency is not recognized
          )

      // Calculate late days
      VAR CurrentDate = TODAY()
      RETURN
          IF (
              CurrentDate > ExpectedInvoiceDate,
              DATEDIFF(CurrentDate, ExpectedInvoiceDate, DAY),  // Calculate the difference in days
              0  // If not late, return 0 days
          )
       
      However it's giving me an error

       

      Any help would be appreciated

       
      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi mayurbajaj ,

         

        I guess the problem is with the switch statement, I didn't find information about BillingFrequency in your screenshot.

         

        Hope it helps!

         

        Best regards,
        Community Support Team_ Scott Chang

         

        If this post helps then please consider Accept it as the solution to help the other members find it more quickly.