Forum Discussion
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 t...
Hi mayurbajaj ,
Please provide sample data in table format and show your expected results.
Best regards,
Community Support Team_ Scott Chang
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
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 ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
The Billing Frequency is basially 'BILL CYCLE' within the screen shot