Forum Discussion
Help with calculating time ???
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.
- rf276 years agoRegular Visitor
Hi Ken,
Thanks for your reply... I'll give it a go..
Richard
- rf276 years agoRegular 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
- kentyler6 years agoSolution 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]