Calculate difference in running hours for an asset over a period of time

[Anonymous]

need a way to calculate the difference between two running hours values over a period of time

The period of time will be controlled with a slicer (which is no problem)

here is the data

[edhans]

I don't see any hours. Just days. Can you clairify, and post data that we can copy into Power BI Desktop to play with vs an image that would require us to retype a lot of data?

[Anonymous]

Counter_key	Running Hours	Reading Date
01STSL_CNT	48413.31	Monday, June 10, 2019
01RTGL_CNT	1231	Monday, June 10, 2019
01RTGL_CNT	1753.7	Saturday, August 24, 2019
01STSL_CNT	49309.12	Thursday, August 29, 2019
01RTGL_CNT	1852	Thursday, August 29, 2019
01RTGL_CNT	1852	Thursday, August 29, 2019
01RTGL_CNT	1852	Sunday, September 15, 2019
01STSL_CNT	49521.02	Wednesday, September 25, 2019
01RTGL_CNT	2214	Wednesday, September 25, 2019
01STSL_CNT	49953.39	Tuesday, October 29, 2019
01RTGL_CNT	2482	Tuesday, October 29, 2019
01RTGL_CNT	2785	Monday, December 2, 2019
01RTGL_CNT	2786	Saturday, December 7, 2019
01STSL_CNT	50454.85	Tuesday, December 10, 2019
01STSL_CNT	50784	Thursday, January 2, 2020
01RTGL_CNT	2992	Thursday, January 2, 2020

[Anonymous]

need a way to calculate the difference between two running hours values over a period of time for a counter key.

I have a file with 100+ counter keys running hours over 2 years time.

[edhans]

Ok, I see the hours. I still am not clear on what you want. 

---

@Anonymous wrote:

need a way to calculate the difference between two running hours values over a period of time for a counter key.

I have a file with 100+ counter keys running hours over 2 years time.

---

Let's take the 01RTGL_CNT key.

Counter_keyRunning HoursReading Date

01RTGL_CNT	1231	6/10/2019
01RTGL_CNT	1753.7	8/24/2019
01RTGL_CNT	1852	8/29/2019
01RTGL_CNT	1852	8/29/2019
01RTGL_CNT	1852	9/15/2019
01RTGL_CNT	2214	9/25/2019
01RTGL_CNT	2482	10/29/2019
01RTGL_CNT	2785	12/2/2019
01RTGL_CNT	2786	12/7/2019
01RTGL_CNT	2992	1/2/2020

What is your expected result? What are the "two running hours values" you are referring to?

[Anonymous]

I want a table that calculates the difference in running hours for a period of time for a list of counter-keys

The period of time is set dynamicly with a slicer

does that make more sense?

[edhans · Accepted solution]

---

@Anonymous wrote:

I want a table that calculates the difference in running hours for a period of time for a list of counter-keys

The period of time is set dynamicly with a slicer

does that make more sense?

---

Yes. that helps. See if the following works for you:

Hours Difference Between Dates = 
VAR TheFirstAmount =
    CALCULATE(
        MIN(Metrics[Running Hours]),
        FILTER(
            'Date',
            MIN(Metrics[Reading Date]) >= FIRSTDATE(ALLSELECTED('Date'[Date]))
        )
    )
VAR TheLastAmount =
    CALCULATE(
        MAX(Metrics[Running Hours]),
        FILTER(
            'Date',
            MAX(Metrics[Reading Date]) <= LASTDATE(ALLSELECTED('Date'[Date]))
        )
    )
RETURN
TheLastAmount - TheFirstAmount

You can see the PBIX file I worked with here. As far as I can tell, when I move the slicer it pics the dates between the dates in the slicer and calculates the difference. I added a date table since you are working with dates, which you can also see in the PBIX file.

[amitchandak]

Please find the formula. For each Asset , taken  Min and Max date. Found there reading. And taken Diff. Not tested. Please, do necessary changes on formula pad

Total value = Calculate(
    sumx(  
        ADDCOLUMNS( 
            SUMMARIZE(
                data,
                data[Counter_key],
				"max_date",
				 MAX( 'Date'[Reading Date]),
				 "Min_date",
				 Min( 'Date'[Reading Date]),
                "Max_ID", max(data[Counter_key])
            ),
            "MaXValue",
            calculate(
                Max(data[Running Hours]),
                filter(
                    all(data),
                    data[Counter_key]= [Max_ID] && data[Reading Date]= ([max_date]) )
                ),
			"MinValue",
            calculate(
                Max(data[Running Hours]),
                filter(
                    all(data),
                    data[Counter_key]= [Max_ID] && data[Reading Date]= ([min_date]) )
                )
            )
        ,[MaXValue] -[MinValue]
    )
)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601