Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
mshodge
Frequent Visitor

Calculating Hourly Usage Data based on last entry

Seems like a simple problem however I cannot find a solution.  Can anyone assist?  

Operators use a piece of equipment and then enter a series of checks for mainteance Items.  Within that they enter the meter reading.  To tabulate how much our entire fleet is being used and be able to slice it with any given day I need the usage field to populate with the amount of hours used based upon the last meter reading.  I could set up a separate table with starts/ends of each week to get the max and min but this seems like a workaround.  Matt

 

DateTimeStampVehicleReadingUsage
1/1/2017 08:00:00a10
1/1/2017 08:00:01b20
1/1/2017 08:00:02c30
1/1/2017 16:00:03a43
1/1/2017 16:00:04b53
1/1/2017 16:00:05c63
1/2/2017 08:00:06a106
1/2/2017 08:00:07b116
1/2/2017 08:00:08c126
1/2/2017 16:00:09a155
1/2/2017 16:00:10b165
1/2/2017 16:00:11c175
1 ACCEPTED SOLUTION
mshodge
Frequent Visitor

Sorry, none of the responses below I was able to get working.  I even bought the Russo and Ferrari book to try to understand the Earlier Function.

 

I solved it within the source data.

 

=IF(ISBLANK(D2),"0",Iferror(D2-large(QUERY($C$2:D2,"Select D where C matches '"&C2&"' order by D desc limit 2"),2),0))

 

Line C is my vehicle ID and Line D is the hour reading the operators input each day. 

View solution in original post

3 REPLIES 3
mshodge
Frequent Visitor

A response I received on email.  Couldn't get it to work.

 

Matt

 

Column = CALCULATE(MAX('T2'[Type]),FILTER(ALL('T2'),'T2'[ID]=EARLIER('T1'[ID]) &&'T2'[Date]<=RELATED(T1[Date])))

mshodge
Frequent Visitor

Sorry, none of the responses below I was able to get working.  I even bought the Russo and Ferrari book to try to understand the Earlier Function.

 

I solved it within the source data.

 

=IF(ISBLANK(D2),"0",Iferror(D2-large(QUERY($C$2:D2,"Select D where C matches '"&C2&"' order by D desc limit 2"),2),0))

 

Line C is my vehicle ID and Line D is the hour reading the operators input each day. 

v-ljerr-msft
Microsoft Employee
Microsoft Employee

Hi @mshodge,

 

According to your description above, you should be able to use the formula below to create a new measure to calculate the usage with the amount of hours used based upon the last meter reading and show it on the report with other fields.Smiley Happy

NewMeasure =
VAR lastReading = MAX ( Table1[Reading] )
RETURN
    CALCULATE ( SUM ( Table1[Usage] ), Table1[Reading] = lastReading )

 

Regards

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.