Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@verowin 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI 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?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCounter_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 |
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.
Ok, I see the hours. I still am not clear on what you want.
@verowin 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?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI 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?
@verowin 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingPlease 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |