Forum Discussion

j5701's avatar
j5701
New Member
2 years ago
Solved

Creating visualization with historical context but only real-time data

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 TypeProductStateCreated Date
LaptopLatitude 5420 - i5In Use4/20/2022 17:28
LaptopLatitude 5400 - i7In Use4/20/2022 17:28
LaptopLatitude 5400 - i5In Use4/20/2022 17:28
LaptopLatitude 5400 - i5In Use4/20/2022 17:28
LaptopLatitude 7420 - i5In Use4/20/2022 17:28
LaptopLatitude 5440 - i5In Use5/15/2024 17:31
LaptopThinkPad T14 Gen 2In Use5/2/2024 13:29
LaptopThinkPad T495sIn Use5/2/2024 13:29
LaptopLatitude 5440 - i5In Use11/9/2023 9:56
LaptopLatitude 5440 - i5In Use11/9/2023 9:56
LaptopLatitude 5430 - i5In Use5/19/2023 12:00
LaptopLatitude 5430 - i5In Use5/19/2023 12:00
LaptopLatitude 5430 - i5In Use3/28/2023 11:55
LaptopLatitude 5430 - i5In Use3/28/2023 11:55
LaptopLatitude 5430 - i7In Use2/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?

Need Help
Tips and Tricks
Tutorial Requests
  • DataNinja777's avatar
    DataNinja777
    2 years ago

    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,

2 Replies

  • DataNinja777's avatar
    DataNinja777
    Super User
    2 years ago

    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's avatar
      j5701
      New Member
      2 years ago

      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!