topic Need Help with Virtual Tables in DAX Commands and Tips

Need Help with Virtual Tables

mayurbajaj — Wed, 22 May 2024 05:02:24 GMT

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.

Re: Need Help with Virtual Tables

Anonymous — Thu, 23 May 2024 02:02:22 GMT

Hi mayurbajaj ,

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

Best regards,
Community Support Team_ Scott Chang

Re: Need Help with Virtual Tables

mayurbajaj — Thu, 23 May 2024 04:55:37 GMT

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

Re: Need Help with Virtual Tables

Anonymous — Thu, 23 May 2024 06:56:33 GMT

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.

Re: Need Help with Virtual Tables

mayurbajaj — Thu, 23 May 2024 06:53:23 GMT

The Billing Frequency is basially 'BILL CYCLE' within the screen shot