average of a virtual table

[francoisl]

HI all, 

I have problemes figuring out a specific problem. 

I have a table structured this way: 

cust_num   

invoice_num 

invoice_date

payment_date. 

if I want to know the customer AR position at a specific date I do a sum of all the invoices where the invoice_date is less or equal to the date to analyse and payment_date is after the date to analyse. 

Now I want to create a virtual table by date on which I want to make an average of let's say 365 days. The average of account receivable during a specific period is important for ratio calculation. 

Any ideas ??

Many thanks

F

[v-yalanwu-msft]

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.

[francoisl]

Thanks for the answer,

Will certainly try this week. 

[francoisl]

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

[AlB]

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?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[francoisl]

I have added some info.  Please review... 

Thanks

F