Forum Discussion

rf27's avatar
rf27
Regular Visitor
6 years ago

Help with calculating time ???

I'm very new to this.. I tried a couple of things... 

 
 

 

14 Replies

  • kentyler's avatar
    kentyler
    Solution 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

    1. 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
    2. 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
    3. 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.
    4. 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.
    • rf27's avatar
      rf27
      Regular Visitor

      Hi Ken,

       

      Thanks for your reply...  I'll give it a go..

       

      Richard

    • rf27's avatar
      rf27
      Regular 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

      • kentyler's avatar
        kentyler
        Solution 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]

  • Jimmy801's avatar
    Jimmy801
    Community Champion

    Hello rf27 

     

    What should the expected output be?

    These are time, and having total of times doesn't seem to be of any sense to me.

    To me this seems more a topic for power query rather then DAX

     

    All the best

     

    Jimmy

    • rf27's avatar
      rf27
      Regular 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

      • Jimmy801's avatar
        Jimmy801
        Community 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