Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DJKarma
Frequent Visitor

Calculate Ageing by Category based on Dates

Unit IDCreatedDateOldValueNewValue
    
    
DJ19 Mar 2022ReleasedSold
DJ21 Mar 2022SoldReleased
DJ21 Mar 2022ReleasedBOOKED
DJ21 Mar 2022BOOKEDReleased
DJ23 Mar 2022ReleasedBOOKED
DJ23 Mar 2022BOOKEDSold
DJ27 Apr 2022SoldCancelled
DJ28 Apr 2022CancelledRestricted
DJ10 Aug 2022RestrictedReleased
DJ26 Aug 2022ReleasedBOOKED
DJ26 Aug 2022BOOKEDSold
DJ29 Sep 2022SoldCancelled
DJ3 Nov 2022CancelledInventory
DJ3 Nov 2022InventoryReleased
DJ3 Nov 2022ReleasedSold
DJ5 Nov 2022SoldReleased
DJ6 Nov 2022ReleasedSold
DJ7 Nov 2022SoldReleased
DJ8 Nov 2022ReleasedSold
DJ9 Nov 2022SoldCancelled
DJ17 Nov 2022CancelledInventory
DJ17 Nov 2022InventoryReleased
DJ18 Nov 2022ReleasedSold

 

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 IDCreatedDateOldValueNewValueAgeingChange
DJ19-Mar-22ReleasedSold0Released-->Sold
DJ21-Mar-22SoldReleased2Sold-->Released
DJ21-Mar-22ReleasedBOOKED0.0Released-->BOOKED
DJ21-Mar-22BOOKEDReleased0.0BOOKED-->Released
DJ23-Mar-22ReleasedBOOKED2.0Released-->BOOKED
DJ23-Mar-22BOOKEDSold0.0BOOKED-->Sold
DJ27-Apr-22SoldCancelled35.0Sold-->Cancelled
DJ28-Apr-22CancelledRestricted1.0Cancelled-->Restricted
DJ10-Aug-22RestrictedReleased104.0Restricted-->Released
DJ26-Aug-22ReleasedBOOKED16.0Released-->BOOKED
DJ26-Aug-22BOOKEDSold0.0BOOKED-->Sold
DJ29-Sep-22SoldCancelled34.0Sold-->Cancelled
DJ03-Nov-22CancelledInventory35.0Cancelled-->Inventory
DJ03-Nov-22InventoryReleased0.0Inventory-->Released
DJ03-Nov-22ReleasedSold0.0Released-->Sold
DJ05-Nov-22SoldReleased2.0Sold-->Released
DJ06-Nov-22ReleasedSold1.0Released-->Sold
DJ07-Nov-22SoldReleased1.0Sold-->Released
DJ08-Nov-22ReleasedSold1.0Released-->Sold
DJ09-Nov-22SoldCancelled1.0Sold-->Cancelled
DJ17-Nov-22CancelledInventory8.0Cancelled-->Inventory
DJ17-Nov-22InventoryReleased0.0Inventory-->Released
DJ18-Nov-22ReleasedSold1.0Released-->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?

2 REPLIES 2
v-stephen-msft
Community Support
Community Support

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]

 

29.png

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

28.png

 

 

 

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.

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)

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors