Calculating past late due amounts based on expected date and received date

[russd]

I have a Purchase Order table that shows the date requested and the date we received it. I want to be able to go back in time and calculate the past due amounts at the end of each month. I can't find a way to attach my test pbi file so I'll put in the data here.

PO Number	Requested Date	Qty	Unit Price	Date received
123	1/3/2023	3	2.25	1/3/2023
654	1/8/2023	5	1.25	2/1/2023
185	2/15/2023	10	2.88	2/23/2023
324	2/18/2023	7	3.5	3/2/2023
12233	3/1/2023	1	2	3/4/2023
875	3/15/2023	4	2.45	3/20/2023
488	4/4/2023	2	1.63	5/2/2023
988	4/25/2023	8	1.99	4/28/2023
12303	5/1/2023	6	0.87	5/15/2023

Example Date Table

[FreemanZ · Accepted solution]

hi @russd 

Not sure if i fully get you, please try to:

1) add a calculated table lilke:

dates = CALENDARAUTO()

Note: don't join the tables.

2) plot a visual with the dates[date] column and a measure like:

due amount = 
VAR _date = MAX(dates[date])
RETURN
SUMX(
    FILTER(
        data,
        data[Requested Date]<=_date
            &&data[Date Received]>=_date
    ),
    data[Qty]*data[Unit Price]
)+0

it worked like:

[Ajendra]

Hey,

1) First of all add calculated column in your data.

2) Then try this measure.

[russd]

Thank you for your reply. This does not give me the output I requested. I'm in the month of May 2023, my fact table updates daily. I want to be able to see what my late total was back in Jan 2023 based on the date I expected my Purchase Orders to arrive and the date that I received them.