Calculate Ageing by Category based on Dates

[DJKarma]

Unit ID	CreatedDate	OldValue	NewValue
DJ	19 Mar 2022	Released	Sold
DJ	21 Mar 2022	Sold	Released
DJ	21 Mar 2022	Released	BOOKED
DJ	21 Mar 2022	BOOKED	Released
DJ	23 Mar 2022	Released	BOOKED
DJ	23 Mar 2022	BOOKED	Sold
DJ	27 Apr 2022	Sold	Cancelled
DJ	28 Apr 2022	Cancelled	Restricted
DJ	10 Aug 2022	Restricted	Released
DJ	26 Aug 2022	Released	BOOKED
DJ	26 Aug 2022	BOOKED	Sold
DJ	29 Sep 2022	Sold	Cancelled
DJ	3 Nov 2022	Cancelled	Inventory
DJ	3 Nov 2022	Inventory	Released
DJ	3 Nov 2022	Released	Sold
DJ	5 Nov 2022	Sold	Released
DJ	6 Nov 2022	Released	Sold
DJ	7 Nov 2022	Sold	Released
DJ	8 Nov 2022	Released	Sold
DJ	9 Nov 2022	Sold	Cancelled
DJ	17 Nov 2022	Cancelled	Inventory
DJ	17 Nov 2022	Inventory	Released
DJ	18 Nov 2022	Released	Sold

This is the table i have to track inventory of a factory.

I need to create two columns in powerbi near it.
They should calculate the ageing based on Unit. In this case , this is just a history of DJ unit, there are 1000's of units with this many rows.

This is how it should look -

Unit ID	CreatedDate	OldValue	NewValue	Ageing	Change
DJ	19-Mar-22	Released	Sold	0	Released-->Sold
DJ	21-Mar-22	Sold	Released	2	Sold-->Released
DJ	21-Mar-22	Released	BOOKED	0.0	Released-->BOOKED
DJ	21-Mar-22	BOOKED	Released	0.0	BOOKED-->Released
DJ	23-Mar-22	Released	BOOKED	2.0	Released-->BOOKED
DJ	23-Mar-22	BOOKED	Sold	0.0	BOOKED-->Sold
DJ	27-Apr-22	Sold	Cancelled	35.0	Sold-->Cancelled
DJ	28-Apr-22	Cancelled	Restricted	1.0	Cancelled-->Restricted
DJ	10-Aug-22	Restricted	Released	104.0	Restricted-->Released
DJ	26-Aug-22	Released	BOOKED	16.0	Released-->BOOKED
DJ	26-Aug-22	BOOKED	Sold	0.0	BOOKED-->Sold
DJ	29-Sep-22	Sold	Cancelled	34.0	Sold-->Cancelled
DJ	03-Nov-22	Cancelled	Inventory	35.0	Cancelled-->Inventory
DJ	03-Nov-22	Inventory	Released	0.0	Inventory-->Released
DJ	03-Nov-22	Released	Sold	0.0	Released-->Sold
DJ	05-Nov-22	Sold	Released	2.0	Sold-->Released
DJ	06-Nov-22	Released	Sold	1.0	Released-->Sold
DJ	07-Nov-22	Sold	Released	1.0	Sold-->Released
DJ	08-Nov-22	Released	Sold	1.0	Released-->Sold
DJ	09-Nov-22	Sold	Cancelled	1.0	Sold-->Cancelled
DJ	17-Nov-22	Cancelled	Inventory	8.0	Cancelled-->Inventory
DJ	17-Nov-22	Inventory	Released	0.0	Inventory-->Released
DJ	18-Nov-22	Released	Sold	1.0	Released-->Sold

The aim is basically calculate ageing, of the Unit in each value, as in how many days was it in Sold value, Cancelled Value.
This is per row level for each unique unit, in this case its DJ only, but thr exists more than that, also the date sorting needs to be addressed before calculating ageing is what i believe.

Can any logic king help me out herE?

[v-stephen-msft]

Hi @DJKarma ,

Sorry, I have no idea about your Ageing column. How was it calculated?

About your Change column, you can create a calculated column.

Change = [OldValue]&"→"&[NewValue]

If possible, explain how 0, 2, 0.0, 0.0 and 2.0 in the Ageing column are calculated.

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.

[DJKarma]

you can see the ageing is calculated for date difference between two rows with same status, you can check and you will know it.

Also this is only for unit DJ, so the calculation should not only take the earlier date in consideration before subtracting (like it is been sorted in this case) but also the same unit (DJ in this case)