The value of invoices collected in the next 12 months from report date

[Anonymous]

Hello 

Please help me, if possible with a solution for this case:

I have a table called "account balance" where i have all the invoices with due date less and greater than 90 days.I also have a table with all invoices collected in the next 12 months called collections (seccond table).

I want to calculate for each interval the sum of the invoices collected in the next 12 months from report date and brign the values into a main table linke in the table below where 400 is the value of invoice A+C and 250 the value of invoice D

Report	Amount collected for invoices greater than 90 days
1/10/2020	400
1/11/2020	250

Account balance

Report date	Invoice number	<90 days	> 90 days
1/10/2020	Invoice A	0	100
1/10/2020	Invoice B	200	0
1/10/2020	Invoice C	0	300
1/10/2020	Invoice D	0	250
1/10/2020	Invoice E	0	310
1/11/2020	Invoice A	0	100
1/11/2020	Invoice K	0	120
1/11/2020	Invoice C	0	300
1/11/2020	Invoice D	0	250

Collections

Collection date	Invoice number	Collected value
6/15/2020	Invoice A	100
7/18/2020	Invoice X	90
9/18/2020	Invoice Y	120
11/18/2020	Invoice C	300
3/11/2021	Invoice Z	250
ETC	ETC	ETC
ETC	ETC	ETC

[Icey]

Hi @Anonymous ,

Could you share some details to help me understand your calculation logic?

1. For "Report" 1/10/2020, is the expected result "400" the sum of the two lines highlighted below?

2. There is no record for "Invoice D" in "Collections" table, how do you get the result of "250"?

3. How do you determine whether the "Collections" belonged to "Report" 1/10/2020 or "Report" 1/11/2020?

For example, why can't 400 be calculated on "Report" 1/11/2020 instead of 1/10/2020?

Best Regards,

Icey

[Anonymous]

Hello Icey

For first question - yes, the amount expected is 400.

I will try to clarify through this picture where i think i explaind better.

What i need is to check for each month , which documents were collected within 12 months from the report date.

If at 31.01.20219 i had 5 invoices in account balance and 2 of them were collected in march and april 2019  and the rest of 3 in march 2020, the report should sum only the 2 invoices collected in 2019.

The same operation  for 28.02.2019.Check which invoices present at 28.02.2019 in account balance were collected in a 12 month period.

The final result should be a table with a centralization of the amounts.

Thank you

[Icey · Accepted solution]

Hi @Anonymous ,

Please check:

Collected value Measure = 
 CALCULATE (
    SUM ( COLLECTION[Collected value] ),
    FILTER (
        'COLLECTION',
        COLLECTION[Collection date] <= EOMONTH ( MAX ( BALANCE[Report date] ), 12 )
            && COLLECTION[Invoice] IN VALUES ( BALANCE[Invoice] )
    )
)

Number of Collected invoices = 
COUNTROWS (
    DISTINCT (
        SUMMARIZE (
            FILTER (
                'COLLECTION',
                COLLECTION[Collection date] <= EOMONTH ( MAX ( BALANCE[Report date] ), 12 )
            ),
            [Invoice]
        )
    )
)

Collected invoices = 
CONCATENATEX (
    DISTINCT (
        SUMMARIZE (
            FILTER (
                'COLLECTION',
                COLLECTION[Collection date] <= EOMONTH ( MAX ( BALANCE[Report date] ), 12 )
            ),
            [Invoice]
        )
    ),
    [Invoice],
    ", "
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello 

First, than you for response.

The measure works fine but i encountered a problem and i don't know how to solve it

If in "invoice column" of "Balance" or "Collection" a duplicate value it is found the error from the picture appear.The problem is that i cannot have an one to one relation between these tables because the invoices, if uncollected will apear in each month in account balance , and in the collection table , a document can be collected partially in different dates.

Thank you