Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.

View My Drafts

Avanade - November 2020
Back to discussions
Expand all | Collapse all

Aging Calculation

Jump to Best Answer

  • 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
    ------------------------------
    Digital Acceleration Binge Day. January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 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.
    ------------------------------

    Original Message
    Digital Acceleration Binge Day. January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 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
    ------------------------------

    Original Message
    Digital Acceleration Binge Day. January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 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.
    Thank you in advance!


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

    Original Message
    Digital Acceleration Binge Day. January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 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
    Lead Analytics Architect
    Iteration Insights
    jarid.mckenzie@iterationinsights.com
    ------------------------------

    Original Message
    Digital Acceleration Binge Day. January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 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
    ------------------------------

    Original Message
    Digital Acceleration Binge Day. January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 7.  RE: Aging Calculation

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

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

    Original Message
    Digital Acceleration Binge Day. January 28th 2021 | 11:00 AM - 2:00 PM EST