average of a virtual table

Hi francoisl 

The requirement is not completely clear. Would you want to show an example with some data and the expected result?

Why do you need this in a virtual table? What are you ultimately trying to do?

Thanks for the reply, 

Maybe a virtual table is not the solution but let me explain with a simplistic example. 

Let's say I have a list of account receivables with the following data

customer   invoice   invoice date  payment date      amount

AAAAAA    12345    July 9 2021   Aug 8 2021             225.00

AAAAAA    12346    July 10 2021  Aug 15 2021          325.00

AAAAAA    12347    Aug 17 2021 Aug 31   2021         120.00

The account receivable for this customer at a specific date is : 

Ex: On July 10 would be all invoices created before or on July 10 with payment after July 10  In this case that would apply to invoices 12345 and 12346 (total of 550$ outstanding)

     On Aug 16 however the sum of all invoices created before or on Aug 16 and paid after Aug 16 is 0 because all invoices were paid and the next invoice is on Aug 17.  

Now the requirement is the following: 

For every day of the last 365 days I would like an average of the daily account receivable based on the calculation above. 

Ex: for July 1 to July 8 the AR balance is 0   (8 days) 

     for July 9 the AR balance is 225              (1 day) 

      for July 10 to Aug 8 balance is 550$     (29 days)

     for Aug 8 to Aug 15 the balance is 325$ (8 days)

     for Aug 16 the balance is 0$                   (1 day) 

    for Aug 17 to Aug 31 the balance is 120.$ (14 days)

The average balance  for that customer is roughly 330$ per days.

But I need a table for each day.  Sorry if the requirement was not clear enough. 

F

Hi, francoisl ;

You could create a measure as follows:

1.create a sum measure.

sum-amount = DATEDIFF(MAX([invoice date]),MAX([payment date]),DAY)*MAX([amount])

2.create a averge measure.

average balance = SUMX(ALL('Table'),[sum-amount])/DATEDIFF(DATE(2021,7,1),DATE(2021,8,31),DAY)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.