status over time

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Power BI training and help » Status over time

Status over time

resolvedResolved · High Priority · Version 365

Status over time

I have a data set which lists the status of a device either as active or deactivated. The data is structured as a log entry which makes the data manipulation difficult. The output is to be able to show the total number of days we had deactivated equipment each month.
Devices can be deactivated over multiple months which also makes the data difficult to manage

RE: Status over time

Hi Eni,

Thank you for the forum question.

I do not know how your time stamps are structured but here you will find a way of getting what you want.

Use Power Query Editor to transform your data. Convert the timestamp column to a datetime format if it's not already in that format.
- Add a custom column to extract the month from the timestamp using the `Date.Month` function.

Add a custom column to identify when a device transitions from active to deactivated or vice versa.
- Use Query editor function `List.Accumulate` to calculate the duration of each deactivation period for each device.

- Group the data by the month column created earlier.

- Calculate the sum of deactivation durations for each month using the "Group By" feature in the Query editor or by creating a new table with summarized data.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector


Training courses


Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


Power BI tip:

Master DAX (Data Analysis Expressions)

DAX is the formula language used in Power BI for creating custom calculations and aggregations. Invest time in learning DAX to unlock the full potential of Power BI. Understand functions like CALCULATE, FILTER, and SUMX, as they are fundamental for creating complex calculations and measures.

View all Power BI hints and tips

Server loaded in 0.06 secs.