Forum Discussion
Help with calculating time ???
I'm very new to this.. I tried a couple of things...
14 Replies
- kentylerSolution Sage
DAX does not have a built in concept like "previous row".
To compare a time in one row to a time in the previous row you need to do 3 things
Create an index for the table in power query, using "add column"
Store the index for the current row in a variable
Use lookupvalue() to get the value you want from the previous row by looking for the index -1
In your case you would also have to check that the day in the previous row was the same day. If it was you would have the 2 datetime values you needed to use DATEDIFF() since it can compare by minute if you like.
If you need help with the DAX code, paste in a copy of your data (rather than using an image) and I'll copy and paste it into a power bi file and write some sample code for you.I'm a personal Power Bi Trainer I learn something every time I answer a question
The Golden Rules for Power BI
- Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
- Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
- Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
- Store all your intermediate calculations in VARs when you’re writing measures. You can return the VARs to check on your steps along the way.
- rf27Regular Visitor
Hi Ken,
Thanks for your reply... I'll give it a go..
Richard
- rf27Regular Visitor
Hi Ken,
Is it possible you could send me an example ? I have created the index, but I'm still having issues with this..
Any help is greatly appreciated ....
Richard
- kentylerSolution Sage
How about doing a screen share ? Its easier to look into complex problems that way. Just email me a good date and time and I'll send you a meeting invitation. [email protected]
- rf27Regular Visitor
Hi Jimmy,
Thank you for your reply.
What I'm looking for is two things,
Total hours worked per date
Then Total hours per days worked .
Ex: day one- 6 hrs, day 2- 8hrs, day 3- 10hrs, and day 4- 8hrs
Total 32 hours
- Jimmy801Community Champion
Hello rf27
how the start and how the end time is defined? A was not able to identify it. What I did already mention is that in one day i saw 3 times. So how to identify what is the start and what is the end date?
What would also be important to now, is what exactly is you expected output. I can think of transforming date (poviting start and end) and with this a easy measure in DAX could be created.
Jimmy