Overdue dates calculation

[jmcph]

Hi , 

I am relatively new to PBI, i am trying to compute for the Overdue Dates filtered by months. 

My table are formatted as follows , i have a separate date table: 

Date                       Loan #                             Attribute                             Amount
8/1/2019               A123                                  Release                              5,000

8/25/2019             A123                                 Collection                           1,000

9/20/2019             A123                                 Collection                            600

12/20/2019           A123                                 Collection                            500

Overdue date is simply 60 days after the Release date. My end goal is to compute for the Overdue Date and the Overdue Amount ( Release amount Less Collection ) with Months as my slicer. 

I have tried multiple measures and video tutorials but still having hard time figuring it out. 

I hope you can help me, a proforma measure that i can study will be much appreciated. 

Thank you very much! 

[Anonymous]

Hi @jmcph ,

1.My sample data is this.

Date	Loan #	Attribute	Amount
8/1/2019	A123	Release	5000
8/25/2019	A123	Collection	1000
9/20/2019	A123	Collection	600
12/20/2019	A123	Collection	500
7/5/2019	A124	Release	6000
8/5/2019	A124	Collection	1500
9/15/2019	A124	Collection	650
10/15/2019	A124	Collection	500
9/12/2019	A125	Release	4000
9/20/2019	A125	Collection	600
10/10/2019	A125	Collection	500
12/15/2019	A125	Collection	100

2.Create a separate date table and let Month column sorts by sort column. There is no relationship between two tables.

Calendar = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2019, 8, 1 ), DATE ( 2019, 12, 31 ) ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Sort", MONTH ( [Date] )
)

3.Create a calculated column to calculate the Due date.

Due =
IF ( [Attribute] = "Release", [Date] + 60 )

4.Create a measure to calculate the Overdue Amount.

Overdue Amount = 
VAR ramount =
    IF (
        ISFILTERED ( 'Calendar'[Month] )
            && SELECTEDVALUE ( 'Calendar'[Month] ) = FORMAT ( MAX ( 'Table'[Due] ), "MMM" ),
        SUM ( 'Table'[Amount] )
   )
VAR caomount =
    IF (
        ramount <> BLANK (),
        CALCULATE (
            SUM ( 'Table'[Amount] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[Loan #] ),
                [Attribute] = "Collection"
                    && [Date] <= MAX ( 'Table'[Due] )
            )
        )
    )
RETURN
    ramount - caomount

4.The result is this.

You can check more details from here.

Best Regards,

Stephen Tao

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