# Power BI Exchange

View Only

## Aging Calculation

• #### 1.  Aging Calculation

Silver Contributor
Posted Jul 22, 2019 05:07 AM
Hi,

I am trying to calculate the ageing for my certain stock.
In my business scenario, there is a daily report to show what are the stocks need to take actions, and the requirement is to track the ageing for that stock.
For example, on the 7/17, I have one stock A, so I clear it and it will not show in the 7/19 report, so the age of stock A will be 1. While, on 7/19, I have another new stock B need to be cleared, but I forgot to take action, and it shows on 7/22 report, so the age is 2 days. The sample data shows in the screenshot, and I have manually created the expected results "Age" column.

Anyone can advise how can I get the expected results?
Thanks.

------------------------------
Zhang Haoran
------------------------------

• #### 2.  RE: Aging Calculation Best Answer

Top Contributor
Posted Jul 22, 2019 07:04 AM
Hi @Jake Zhang,

If your table is called 'Table1' then you can write a measure to calculate Age:

```Age =
CALCULATE(
COUNTROWS( Table1 ),
FILTER(
ALL(Table1[Date]),
Table1[Date] <= MAX( Table1[Date] )
)
)```

This measure works in a table visual but if you need to add it as a column for whatever reason then you need something slightly different:

```Age Column =
VAR myPart = [Part]
VAR myDate = [Date]
RETURN
CALCULATE(
COUNTROWS( Table1 ),
FILTER(
ALL( Table 1 ),
Table1[Part] = myPart
&&
Table1[Date] <= myDate
)
)​```

Hope that helps.

------------------------------
Simon Lamb
IT Consultant, Method Excel Ltd.
------------------------------

• #### 3.  RE: Aging Calculation

Silver Contributor
Posted Jul 22, 2019 09:58 AM
Hi @Simon Lamb

Thank you so much! I'm too bad at DAX :(
I am using your 2nd calculated column method since I need to visualize in a bar chart, and it works perfectly.

------------------------------
Zhang Haoran
------------------------------

• #### 4.  RE: Aging Calculation

Silver Contributor
Posted Oct 08, 2019 10:09 AM
Hi @Simon Lamb,

Sorry to trouble you again.
I've already used your option 2 for a while but found out I missed out one scenario here and appreciate if you could help.

The picture is showing the expected results of ageing. The one I've missed out is the last row.

I have the stock ABC from 1st July to 3rd July, so I've cleared this stock on 4th July means no stock for this part. However, on the 5th July, it comes out again, so the ageing need to restart from 1.

How can I achieve this by using DAX? It will be best if just need to add in some code on the top of your option 2.

------------------------------
Zhang Haoran
------------------------------

• #### 5.  RE: Aging Calculation

Bronze Contributor
Posted Oct 08, 2019 06:02 PM
Hey Zhang,

I needed to do this in two calculated columns.

`NewAgingIndicator =`
` VAR _thisPart = [Part]`
` VAR _thisDate = [Date]`
` VAR _PART =`
`     FILTER ( 'Table', 'Table'[Part] = _thisPart && 'Table'[Date] = _thisDate - 1 )`
` VAR _newInd =`
`     IF ( ISBLANK ( COUNTROWS ( _PART ) ), 1, 0 )`
` RETURN`
`     _newInd`

`Aging =`
` VAR _thisPart = [Part]`
` VAR _thisDate = [Date]`
` VAR _maxDate =`
`     CALCULATE (`
`         MAX ( 'Table'[Date] ),`
`         'Table'[Part] = _thisPart,`
`         'Table'[Date] < _thisDate,`
`         'Table'[NewAgingIndicator] = 1`
`     )`
` VAR _rows =`
`     FILTER (`
`         'Table',`
`         'Table'[Part] = _thisPart`
`             && 'Table'[Date] <= _thisDate`
`             && 'Table'[Date] >= _maxDate`
`     )`
` RETURN`
`     IF ( 'Table'[NewAgingIndicator] = 1, 1, COUNTROWS ( _rows ) )`

------------------------------
Jarid McKenzie
Iteration Insights
jarid.mckenzie@iterationinsights.com
------------------------------

• #### 6.  RE: Aging Calculation

Silver Contributor
Posted Oct 08, 2019 11:21 PM

Thanks @Jarid McKenzie, that's impressive!
However, I do have another question, what if my table doesn't consider Saturday and Sunday, how can I calculate the ageing?​
I guess the logic is something like the below (just to demo the logic and expectation), but for sure "Filter" and "If" cannot put together:
`NewAgingIndicator =`
`VAR _thisPart = [Part]`
`VAR _thisDate = [Date]`
`VAR _PART =`
`    FILTER ( 'Table', 'Table'[Part] = _thisPart && `

`             if(Weekday('Table'[Date])=Monday),``'Table'[Date] = _thisDate - 3,`

`                        'Table'[Date] = _thisDate - 1 ))`
`VAR _newInd =`
`    IF ( ISBLANK ( COUNTROWS ( _PART ) ), 1, 0 )`
`RETURN`
`    _newInd`

------------------------------
Zhang Haoran
------------------------------

• #### 7.  RE: Aging Calculation

Silver Contributor
Posted Oct 09, 2019 11:31 PM
Anyone can help with this? Thanks!

------------------------------
Zhang Haoran
------------------------------