topic Re: Calculating past late due amounts based on expected date and received date in DAX Commands and Tips

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

russd — Thu, 11 May 2023 19:40:36 GMT

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

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

Ajendra — Thu, 11 May 2023 20:38:04 GMT

Hey,

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

2) Then try this measure.

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

russd — Fri, 12 May 2023 13:31:51 GMT

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.

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

FreemanZ — Sat, 13 May 2023 02:20:11 GMT

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: