Duty Cycle from Date table

[mwenski]

This is a PowerBI / Power Query "how to approach" question. I'm really interested in learning what terms I should be throwing into google to find this answer and want to thank those that read this in advance.  If I have a table of dates showing that a light on a room was turned on and off, what's the best way to approach this from a data modeling perspective using Power Query or DAX functions in Power BI?

I have solved this problem in the past using different tools (Tableau and Excel), but I wanted to ask this community to see if I'm following best practices and I'm not trying to shove a round peg in a square hole.

I'm trying to represent a summary of the attached table filtered to a single ID (column A) that has 36 rows (entry order indicated by "CHGNR" or Change Number). It is showing dates a particular status was set ("INACT" is Blank) and reset ("INACT" has an X value, indicating the status has been set to inactive). The UDATE column is an 8 digit integer representing the date in YYYYMMDD format, and I made an excel formula to parse that date if it'll help give an answer.  I'm unclear on how to attach an excel file, I hope the below table format is ok.

Lower Date bound should be 1/1/2011 and the desired upper bound would be the date of the extract, or 4/7/2020.  For background, there are ~20k unique objects in the table, ~77k total entries that meet this criteria, and there exists a second fact table with 1 row per object that contains attributes of interest.

Object	CHGNR	INACT	UDATE	Formatted Date
?0100000000000080898	1		20100913	9/13/2010
?0100000000000080898	2	X	20101007	10/7/2010
?0100000000000080898	3		20110504	5/4/2011
?0100000000000080898	4	X	20110519	5/19/2011
?0100000000000080898	5		20120305	3/5/2012
?0100000000000080898	6	X	20120330	3/30/2012
?0100000000000080898	7		20120504	5/4/2012
?0100000000000080898	8	X	20120601	6/1/2012
?0100000000000080898	9		20130103	1/3/2013
?0100000000000080898	10	X	20130130	1/30/2013
?0100000000000080898	11		20130904	9/4/2013
?0100000000000080898	12	X	20131001	10/1/2013
?0100000000000080898	13		20141103	11/3/2014
?0100000000000080898	14	X	20141124	11/24/2014
?0100000000000080898	15		20150109	1/9/2015
?0100000000000080898	16	X	20150203	2/3/2015
?0100000000000080898	17		20150304	3/4/2015
?0100000000000080898	18	X	20150402	4/2/2015
?0100000000000080898	19		20150702	7/2/2015
?0100000000000080898	20	X	20160106	1/6/2016
?0100000000000080898	21		20160511	5/11/2016
?0100000000000080898	22	X	20160607	6/7/2016
?0100000000000080898	23		20170105	1/5/2017
?0100000000000080898	24	X	20170112	1/12/2017
?0100000000000080898	25		20170302	3/2/2017
?0100000000000080898	26	X	20170327	3/27/2017
?0100000000000080898	27		20180110	1/10/2018
?0100000000000080898	28	X	20180131	1/31/2018
?0100000000000080898	29		20180305	3/5/2018
?0100000000000080898	30	X	20180402	4/2/2018
?0100000000000080898	31		20180502	5/2/2018
?0100000000000080898	32	X	20180524	5/24/2018
?0100000000000080898	33		20180905	9/5/2018
?0100000000000080898	34	X	20181002	10/2/2018
?0100000000000080898	35		20181102	11/2/2018
?0100000000000080898	36	X	20181231	12/31/2018

I'll include the excel reference here in case it's relevant:

Excel: Prep this table by sorting by Object and Change number (smallest number for each functional location first)

Calculate the days in each state by subtracting date of current row with prior row (if same Object). If Status has "X" in "Indicator: Status is Inactive" it represents the date the prior status ended, and the date difference is how long the Object had "Lights out" so to speak. If the last record for the Object has no X (eg, the status was Set to "Lights out") then the presumed end date of that state would be the date of the data extraction.

Another table summarized each functional location with a traditional SUMIF statement, using the number mentioned above as what's being added and the condition of Object row value = Object Column. (There was another formula to see if the status was active at the end of the state and add in those days using the extract date.)

Tableau:

Wrote a formula that basically asked a user for a date (or used Today() if they didn't provide any) as an day to look from (by default it would be today's date, but they could look at the start of the year if desired).

Calculated Date Diff between the date in the table and the user entered date on a per row basis.

Made an integer that is -1 when the Indicator: Status is Inactive = "X" and positive 1 when there is not an X.

Multiplied the prior two values together and summed them per functional location to calculate the total days spent with the status set (EG, total days the "Lights Out").

I did some further refinements to ensure that if a user wanted to focus on a particular range of dates that the calculation only counted dates within that data range, and I don't want to distract from my main question here with those.

[Greg_Deckler]

If I understand this correctly, and I am not saying this is the "best" way, but one way is: See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[mwenski]

Thanks Greg! That's really close to a perfect example.  Right now I don't have Down and Up on the same row, but split across two rows.  I'll poke around at the PowerQuery tutorials on how to manipulate the data to the point that I have 1 row per down/up cycle.

I imagine I could use the odd change number data as "Down" and even as "Up" and perhaps filter even change numbers out, add one to the odd values, join on Object and the modified change number as key. 

I book marked your link for future reference after giving you kudos. I'm interested in seeing if there are any other approaches and then I'll march forth. 

[dax]

Hi @mwenski , 

I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.