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.
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]