PowerBI DAX - Sum table by criteria and date

Question

relatively new to PowerBI/PowerQuery/DAX and have become stuck at the following problem. I am unsure what road to go down to get the best outcome and would appreciate any help.

My data table is connected to a time tracking application. A User will enter a time entry everytime they complete a task. The task can be either a Project task or an Admin task. When selecting either of these, there will be multiple sub-categories beneath each, each with its own ID. This translates to my table as the following :

User    ProjectID    AdminID    Hours    Date
John    1                       2        01/01/22
John                 11         1        01/01/22
John    4                       1        01/01/22
John                 12         3        01/01/22
John                 13         1        01/01/22
Pete                 7          1        01/01/22
Pete    2                       4        01/01/22
Pete    3                       2        01/01/22
Mike    1                       6        01/01/22
Mike                 9          1        01/01/22
Mike                 10         1        01/01/22

My objective is, for each Date in the table, to calculate the total hours spent either doing Project tasks or Admin tasks. I am not concerned about the specific breakdown (ie the sum of the unique IDs), rather the overall total. The above example covers just one day, in reality my data covers multiple years. My expected output will look like this :

User    TotalProject    TotalAdmin    Date
John    3               5             01/01/22
John    3               4             01/02/22
John    5               2             01/03/22
Pete    5               1             01/01/22
Pete    1               8             01/02/22
Pete    6               2             01/03/22
Mike    6               2             01/01/22 
Mike    6               1             01/02/22
Mike    7               2             01/03/22

I am unsure the best method to achieve this - either by creating some kind of column in the table through PowerQuery? Or a calculated column using DAX? And if so, what the SUM syntax would look like?

Very willing to learn, to any tips would be greatly appreciated!

Answer 1

For your sample input, just create 2 measures.

Total Admin = CALCULATE( SUM('Table'[Hours]), NOT(ISBLANK('Table'[AdminID])))

Total Project = CALCULATE( SUM('Table'[Hours]), NOT(ISBLANK('Table'[ProjectID])))