I want to calculate total hour spent by the employee for a day
Please find below the example
I have also included the desired column which I am trying to get.
|Name||Date||Task||Hours spent||Expected Output column|
I assume you have a table called MyTestTable like below.
Then you should be able to use the formula below to create a calculate column to get Hours Spent for a day for a specific Name.
Hour Spent For A Day = VAR name1 = MyTestTable[Name] VAR date1 = MyTestTable[Date] RETURN CALCULATE ( SUM ( MyTestTable[Hours spent] ), ALL ( MyTestTable ), FILTER ( MyTestTable, MyTestTable[Name] = name1 && MyTestTable[Date] = date1 ) )
Thank you @ankitpatira,
But I am afraid that I can not try this way, as in actual senario I am using the data from 3 table which are related to each other (by building the relationship).
So name is coming from Table 1, group is coming from table 2 and hour is coming from table 3.
So i was thinking to do it by using a DAX formula. Because with Dax formula I can use these columns from different table by using the related function.
Filter ( YourTable, YourTable[Name] = Earlier ( YourTable[Name]) && YourTable[Date] <= Earlier(YourTable[Date]) ),
It should work
If this solves your issue please accpet this as a solution and also give KUDOS.
But with the suggested solution from both of you I have following problem:
I am having 2 different table table 1 and table 2 which are connected to each other. and Name and date are coming from two different table.
Hours spent and date is from table 1
name is from table 2
@CheenuSing With earlier I am not able to use the other table's column.
@v-ljerr-msft I don't know if there is a way to create a new calculated column which takes input from two different tables.
I don't know if there is a way to create a new calculated column which takes input from two different tables.