Selectcolumns to make new table and how to sort it

[rpinxt]

So from an existing table I was able to extract this :

The goal is to see by day how many time a person (name) spend on a process.

So for that I want to make a calculated column but then first I need to sort these 4 columns so

that per day, per name and per time I can see which process was worked on.

Because as you now see it it is not correct.

This is what the first name did on Feb 1st:

And also as you see here the sorting is not correct as time is all over the place.

Is there a way I can sort this new table?

So by date, then by name and then by time.

[rpinxt]

Thanks @Daniel29195 this looks very impressive.

Although the partitionby seems to have an error but there is still output.

Put it now on my main table as there was no use for a new table.

Still these values are not entirely correct I guess:

I think the process Apply should show 74 minutes and the Folding Delivery 12 minutes etc.

And also the change in the day causes some problems. 

Should 'reset' at a day level when calculating.

[Daniel29195 · Accepted solution]

@rpinxt

output 

Column = 

var currenttime =  'Table'[Time]
var prev_time = 
SELECTCOLUMNS(
OFFSET(
    -1,
    SUMMARIZE(
        'Table',
        'Table'[Date],
        'Table'[name],
        'Table'[Time]
    ),
    ORDERBY( 'Table'[Date] ,asc, 'Table'[Time] , asc),
    PARTITIONBY('Table'[Name],'Table'[Date])
),"time",'Table'[Time]
)

return  DATEDIFF(prev_time,currenttime,MINUTE)

Column 3 = 
 SELECTCOLUMNS(
OFFSET(
    1,
     SUMMARIZE(
        'Table', 
        'Table'[Name],
         'Table'[Date],
          'Table'[Time] 
          ,'Table'[Column]
        ),
        ORDERBY( 'Table'[Date] ,asc, 'Table'[Time] , asc),
    PARTITIONBY('Table'[Name],'Table'[Date])
    ),"time",'Table'[Column]
)

then use in the visual, column3 

 hope this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[rpinxt]

@Daniel29195 thanks!
Unfortunately I get this on the first column:

Is it an exact copy as you have made in the pbix I sent?

[Daniel29195]

@rpinxt it seems from the image, you ddidnt put the name of the column you are creating ,

you are starting your code with var = 

[Daniel29195]

@rpinxt is it possible to share the pbix file to take a look at what is happening from your side ? 

[rpinxt]

🤦‍

You are right...sorry about that!

Looks indeed to be working.

Thanks a lot!!

[rpinxt]

@Daniel29195 

Yes I uploaded the pbix file here:

https://drive.google.com/file/d/1nV-2HYrQPcUIDiaO8XbZbz--3zHY7qzE/view?usp=drivesdk

Let me know if you cannot access.

[Daniel29195]

@rpinxt 

output

caclulated column added to the table : 

Column = 

var currenttime =  'Table'[Time]
var prev_time = 
SELECTCOLUMNS(
OFFSET(
    -1,
    SUMMARIZE(
        'Table',
        'Table'[DateName],
        'Table'[name],
        'Table'[Time]
    ),
    ORDERBY( 'Table'[DateName] ,asc, 'Table'[Time] , asc),
    PARTITIONBY('Table'[name])
),"time",'Table'[Time]
)

return  DATEDIFF(prev_time,currenttime,MINUTE)

let me know if this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠