Calculate the age of the asset at any selected date

[GeorgeFP]

Good day, and thanks for reading.

I hope you can point me in the right direction for a better solution than my current one, which lacks flexibility. I have been using calculated columns for a few predefined dates and calculating the asset age at that date (i.e., the user can only select a date from the predefined dates), with the PO date always the “start” to calculate the age.

I aim to show the user all the assets (via a table) with their "age" at the date selected from the dropdown. To answer the question:  “Show me the age of all the assets that will be 10 years old on <selected date>”.

The Table: (image of the table attached)

1 x Asset table with

3 x columns a) Asset Name, b) PO number, c) PO date

I appreciate your reading, and thanks in advance

Sincerely, George

[GeorgeFP]

Thank you @danextian - its exactly what I needed.

Regards George

[danextian · Accepted solution]

Hi @GeorgeFP 

Calculating age based on a slicer selection is straightforward, but categorizing them into years while accounting for leap years is quite complex. For the sake of simplicity and illustration, in my sample PBIX, years are simply calculated as age in days divided by 365, rounded down to the nearest integer.

The age is dynamically calculated using a measure referenced to a disconnected dates table. This measure is then materialized by using another table, allowing ages to be sliced, diced, and categorized in visuals.

PO Age in Days =
DATEDIFF (
    // Calculates the difference between two dates
    SELECTEDVALUE ( PO[PO Date] ),
    // Gets the single value of PO Date for the current context
    MAX ( 'As of Date'[Date] ),
    // Gets the maximum date from the 'As of Date' table
    DAY // Specifies that the difference should be in days
)

PO by Age =
CALCULATE (
    COUNTROWS ( PO ),
    // Counts the rows in the PO table
    FILTER (
        // Applies a filter to the table
        VALUES ( PO[PO Date] ),
        // Creates a list of unique PO Dates
        [PO Age in Days]
            IN VALUES ( Ages[Age in Days] ) // Filters the rows where PO Age in Days matches Ages[Age in Days]
    )
)

Please see the sample pbix.

[GeorgeFP]

@danextian 

Wow, no wonder I did not get it right 🙂

I will test immediately and revert with my findings.

Regards George

[v-yiruan-msft]

Hi @GeorgeFP ,

Whether the pbix file provided by danextian can help you get the expected result? If yes, could you please mark his reply as Answered? It would be helpful to others in the community find the solution easily if they face the similar problm

Best Regards