Creating visualization with historical context but only real-time data

[j5701]

Hey all,

I'm trying to create a visualization that will show total assets in the org by month, and the data I have is a table of all assets with the date they were added to our inventory. I'm fairly new to PowerBI, and am at a loss on how to do this with only real-time data. The current solution I have is to manually pull a report each month, and then use the date of the report as the date column in the data. With that said, while this seems to work, I'd rather use the API and cut-out the manual work. I have attached some sample data, and a picture of the visualization below for some context.

 

CI Type	Product	State	Created Date
Laptop	Latitude 5420 - i5	In Use	4/20/2022 17:28
Laptop	Latitude 5400 - i7	In Use	4/20/2022 17:28
Laptop	Latitude 5400 - i5	In Use	4/20/2022 17:28
Laptop	Latitude 5400 - i5	In Use	4/20/2022 17:28
Laptop	Latitude 7420 - i5	In Use	4/20/2022 17:28
Laptop	Latitude 5440 - i5	In Use	5/15/2024 17:31
Laptop	ThinkPad T14 Gen 2	In Use	5/2/2024 13:29
Laptop	ThinkPad T495s	In Use	5/2/2024 13:29
Laptop	Latitude 5440 - i5	In Use	11/9/2023 9:56
Laptop	Latitude 5440 - i5	In Use	11/9/2023 9:56
Laptop	Latitude 5430 - i5	In Use	5/19/2023 12:00
Laptop	Latitude 5430 - i5	In Use	5/19/2023 12:00
Laptop	Latitude 5430 - i5	In Use	3/28/2023 11:55
Laptop	Latitude 5430 - i5	In Use	3/28/2023 11:55
Laptop	Latitude 5430 - i7	In Use	2/22/2023 14:24

 

For example, with the data above, Feb 2023 should show 6 assets as there are 6 devices listed on or before that month. March 2023 would therefore show 8, and May 2023 would show 10, and so on. Is there a way to do this in PowerBI?

[DataNinja777 · Accepted solution]

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,

[j5701]

This definitely lead me on the right direction! Unfortunately the data I'm pulling from can't include a disposal date, do I did the following to work-around it:

Laptops In Use =

SUMX (

    'Table Name',

    IF (

        MAX ( 'Calendar'[Date] ) >= 'Table Name'[Created Date]

            && 'Table Name'[State] = "In Use"

            && 'Table Name'[CI Type] = "Laptop",

        1,

        BLANK ()

    )

)

This isn't perfect, as I realize it will remove items that were in stock or in use from previous months, but seems to be the closest I can get with the data I have.

Thanks so much for your help!