Forum Discussion

giorgilomidze's avatar
giorgilomidze
Icon for Resolver I rankResolver I
8 years ago
Solved

groupby days calculated column

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.

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    HI giorgilomidze,

     

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

     

    Regards,

    Xiaoxin Sheng

    • giorgilomidze's avatar
      giorgilomidze
      Icon for Resolver I rankResolver I
      8 years ago

      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)

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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