Need Help with Virtual Tables

[mayurbajaj]

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.

[v-tianyich-msft]

Hi @mayurbajaj ,

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

Best regards,
Community Support Team_ Scott Chang

[mayurbajaj]

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

[v-tianyich-msft]

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.

[mayurbajaj]

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