Creating visualization with historical context but only real-time data

Hi j5701 ,

There are indeed ways to achieve this in Power BI, and your approach of wanting to track historical changes rather than just snapshot information on the cutoff date is on the right track. Presumably, your company's laptop inventory includes a "Retirement Date" in addition to the "Created Date". You'll apply a similar concept and technique used for tracking headcount or inventory.

To achive this, your raw data needs additional fields such as asset number column to uniquely identify each laptop, and disposal (retirement) date column. This ensures that laptops disposed of after a certain period, like 3 years, are not counted in the stock. Without this information, your laptop data would accumulate over time without accounting for disposals.

The key to achieving a flexible inventory analysis is to create a calendar table and set it as a disconnected table.  Then, you can write a DAX formula similar to the one below:

Laptop in stock =
SUMX (
    'Laptops',
    IF (
        MAX ( 'Calendar'[Date] ) >= 'Laptops'[Created Date]
            && MAX ( 'Calendar'[Date] ) <= 'Laptops'[Disposed Date],
        1,
        BLANK ()
    )
)

This DAX formula calculates the number of laptops currently in stock based on their creation and disposal dates. Adjust the table and column names ('Laptops', 'Calendar', 'Created Date', 'Disposed Date') as per your actual data model in Power BI.

Best regards,