Power BI Exchange

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

Aging Calculation

Jump to Best Answer
  • 1.  Aging Calculation

    Bronze Contributor
    Posted 27 days ago
    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

    Gold Contributor
    Posted 26 days ago
    Hi @Zhang Haoran,

    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

    Bronze Contributor
    Posted 26 days ago
    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
    ------------------------------