Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Good Day All,
Having difficulties getting a Measure to work properly.
Scenario as follows: I am trying to measure how many hours each day each machine is running.
The dataset includes multiple records for multiple machines for each day.
DeviceID | Date | DateTime | Duration (sec) |
HSCP-2149 | 5/3/2022 | 5/3/2022 0:47 | 5925060 |
HSCP-2149 | 5/2/2022 | 5/2/2022 22:44 | 5907960 |
HSCP-2149 | 5/2/2022 | 5/2/2022 21:44 | 5904360 |
HSCP-2149 | 5/2/2022 | 5/2/2022 21:04 | 5902020 |
HSCP-2149 | 5/2/2022 | 5/2/2022 20:46 | 5900940 |
HSCP-2149 | 5/2/2022 | 5/2/2022 20:43 | 5900760 |
HSCP-2149 | 5/2/2022 | 5/2/2022 20:26 | 5899680 |
HSCP-2149 | 5/2/2022 | 5/2/2022 0:43 | 5897160 |
In essence, I need to grab the first value of each day. I have tried the following:
CALCULATE( FIRSTNONBLANK( MyTable[ObdEngineSeconds], SUM(MyTable[ObdEngineSeconds] )))
This works fine when I capture each record individually. When I try to create a Measure with Variables, I am unable to get it to work properly. I do have a Calendar Table connected by Date to this fact table.
My Measure needs to capture the first record of the current day and subtract it from the first record of the previous day.
In the above example the result is: 27,900 seconds / 3600 = 7.75 hours.
Any help or guidance from the Community would be much appreciated.
Much thanks and Regards,
Solved! Go to Solution.
I was able to do it for multiple DEVICE IDs, hope it helps! CHECK THE BOLD ONE
Step 0
Step 3
NEW
Step4
Step 5 Last:
NEW OUTPUT
Regards,
Ritesh
Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users
Did a lot of hard work to solve it, 🙂 Hope it helped you, if not please let me know, would not like to leave this mid-way.
Regards,
Ritesh
@rsbin Hope it helped, appreciate your response here so that we can close the thread
Regards,
Ritesh
Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users
Good Morning,
Your solution did help some, but I haven't been able to complete the puzzle as yet.
Because I am dealing with multiple machines ( over 100 ) at this point, I need another filter in there for Device.
I haven't been able to determine why, but I haven't been able to fully replicate your solution.
Would like to keep the thread open until I can get to a proper solution.
Thanks for your input, as I keep working on this.
Kind Regards,
Sure, I get what you say, that should also work (multiple DEVICE ID), as you say need to add another filter, I will also check meanwhile, we need to group it with Device ID as well but I think you will be able to crack 🙂
Regards,
Ritesh
I was able to do it for multiple DEVICE IDs, hope it helps! CHECK THE BOLD ONE
Step 0
Step 3
NEW
Step4
Step 5 Last:
NEW OUTPUT
Regards,
Ritesh
Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users
User | Count |
---|---|
122 | |
109 | |
97 | |
60 | |
57 |
User | Count |
---|---|
138 | |
116 | |
102 | |
70 | |
57 |