Count Items at most recent status in Power BI

[dpc_development]

I have data in a table similar to that in the image below.

For each machine, there is a row showing when the in-use status changes over time. If the machine is in-use, the 'In Use' column equals 1, else the Offline column equals 1.

I request suggestions on how to best track daily status of machine in use. In other words, I need to know on a per day basis how many machines are in use or offline.

Example output: (The .. are all days in sequence, with the same value)

I guess one way would be to perform an ETL where I calculate the total count of in-use / offline, for every day of the year, and use a Calendar table to retrieve the value as on a particular date. This would really blow up the number of rows to process, especially when I include dimension columns.

I guess another way would be to use a Calendar table without a relation to the Fact table in Power BI, and use its dimension value to get the most recent Item row and count that using DAX.

Something that potentially complicates this is the presence of other metric columns. The table may put a '1' on another column, and show 0 for both in-use and offline. I could get the MAX(fact_table[date]) twice - one where [in-use] = 1 and one where [offline] = 1, compare which of the two dates are lower and do SUMX on that accordingly.

Has anyone faced a similar problem in the past? What do you think is the most efficient solution?

[amitchandak]

@dpc_development , If there is not an entry in the first table, does that means offline. if so then we need work with date table .

If not, means you have daily entry then it just a sum of In use and offline.

Can you confirm the two cases?

[dpc_development]

Hi @amitchandak, firstly I control the output of the table above, so the schema can be modified to better suit Power BI measure calculation.

That said, the first instance of any item will always have a 1 in either In-Use or Offline, and there will be at least one entry for all the possible items.

In reality the raw table is similar to the image below.

Using the above format, various key status values, in addition to In-Use / Offline, of the item can be tracked. The net sum of PQR in the last two rows (20 Mar) is zero. This is a way in which the original transaction table reverses an erroneous transaction.

In other words, in the root table, a missing value is the action of nothing happening and a zero value is value to ignore. I have written a view to show the starting status values explicitly and hence you see two columns, with the offline column showing 1 for the other two items.

[amitchandak]

@dpc_development , One more question, will consider ABC in use from 1st to 15th?

[dpc_development]

@amitchandak Yes. There is a new row for every change in status. So until a new row is found, the previous one is valid. Since there is an offline entry for 15th, the item should be considered in use on all days in between 1 and 14.

This is also depicted in the example output of the original question. That is the final output that feeds into a chart or another visual.