groupby days calculated column

[giorgilomidze]

i have task on which people are working. tasknumbers are uniq. per day there can be several tasks person can work on. lets take that person worked on 3 taks and sum of hours he worked is 6 hours. It means that person was 18 hours free ( 24-6 they have 24 H shifts).  i have task starting time and task ending time. with help of datediff i have hours he has worked. 

i need to get sum of hours he was free each day in matrix. rows-name; columns -date. when i subtract 24-[time he has worked], if he has worked more than 1 task,lets take 3 tasks, it menas that he will have 72 working hours -6 houres worked , thus 66 hours free and 6 hours worked.

[v-shex-msft]

HI @giorgilomidze,

It will be help if you share some sample data/pbix file and expected result for analysis.

Regards,

Xiaoxin Sheng

[giorgilomidze]

there is shown one persons working hours. task time is difference between validfrom and validto. valid from is when task has started and valid to is when task was finished.

i whant to see what amount of time one person has per day in matrix visual. ( there can be days when person can have negative free time, becouse he was assigned to more than one task at the same time)

[v-shex-msft]

HI @giorgilomidze,

Can you share us sample data share with excel or pbix file. It is hard to import data from your screenshots.

Regbards,

Xiaoxin Sheng

[giorgilomidze]

Google Drive Execel File. I dont know how to upload. Here is Google Drive link for Excel. 

[v-shex-msft · Accepted solution]

Hi @giorgilomidze,

You can try to use below measure if it suitable for your requirement:

Free Hours = 
VAR _current =
    DATEVALUE ( MAX ( [VALIDFROM] ) )
VAR total =
    SUMX (
        FILTER (
            ADDCOLUMNS (
                Sheet1,
                "Daily Diff", DATEDIFF ( [VALIDFROM], DATEVALUE ( [VALIDFROM] ) + 1, HOUR )
            ),
            [NAME] = SELECTEDVALUE ( Sheet1[NAME] )
                && DATEVALUE ( [VALIDFROM] ) = _current
        ),
        [Daily Diff]
    )
RETURN
    IF ( total > 6, total - 6 )

Regards,

Xiaoxin Sheng