undefined

[Gurukiran003]

In  below image there is cloumn Name which name of empolyee N1 for whom the work has assigned from 1-1-2020 and 3-1-2020 and overal work is 20 , here need to find the average and in below table he has take Previlaged Leave on 1-1-2020 so average of workingday will be 10(20/number of day exculding Previlaged Leave) if he has not taken any leave thn average is 6.67 ( 20/number of days i.e 3 because from project start date to end date is value is 4 so (20/3=6.67)) How to get it in power bi .

[VijayP]

@Gurukiran003 

I think you need to rephrase the problem Statement. It is not clear .
Image cannot be rotated. Some Sample Data will give more clarity alongwith the Problem Statement

[Gurukiran003]

I have two table one Task Table and 2nd one is empolyee leave taken Table

in Task table it show the name of the empolyee who working on which task and also its show start date and end date of project along with overal work hours, 

here we need to find average of work hours example if he worked from 1-jan to 5- jan 2020 then number of days is "5 days" then overal work hour is 15 then average is 15/5=3( only possible if that person is not on leave during these 5days) , incase he to a leave on 1-jan-2020 and 4-jan-2020 then out 5days he was absent for 2days that means he worked only 3days i.e 2-jan-2020,3-jan-2020 and 5-jan-2020, now the average work is 15/3=5, that value i have to show in date wise i.e 

1-jan-2020 = leave has to show 2-jan-2020 = 5 , 3-jan-202 =5 , 4-jan-2020 =Leave and 5-jan-2020 = 5, hope this is ok  the value has to show in leave tracker.

[Anonymous · Accepted solution]

Hi @Gurukiran003 

Here I will give you some advice on transforming data model and how to build measures.

Firstly, we need to build a calculated table to build a matrix visual like left up corner.

Table =
GENERATE (
    SUMMARIZE ( TASK, TASK[NAME], TASK[TASK], TASK[SUB] ),
    ADDCOLUMNS (
        CALENDARAUTO (),
        "YEAR", YEAR ( [Date] ),
        "MONTH", MONTH ( [Date] ),
        "DAY", DAY ( [Date] )
    )
)

Then we need to expand leave date in Employee leave Table.

For reference: Expand a date range

We need Subtraction +1, this is the difference between our case and sample in above blog.

New Employee Leave Table.

Then we add a Leave Flag column in calculated table.

Leave Flag = 
IF('Table'[Date] IN VALUES('EMPLOYEE LEAVE'[Leave Day]),"L")

Then build measure as below.

Measure = 
VAR _START = CALCULATE(MAX(TASK[START]),FILTER(ALL(TASK),TASK[NAME]=MAX('Table'[NAME])&&TASK[TASK]=MAX('Table'[TASK])&&TASK[SUB]=MAX('Table'[SUB])))
VAR _END = CALCULATE(MAX(TASK[END]),FILTER(ALL(TASK),TASK[NAME]=MAX('Table'[NAME])&&TASK[TASK]=MAX('Table'[TASK])&&TASK[SUB]=MAX('Table'[SUB])))
VAR _DayDiff = CALCULATE(COUNT('Table'[Date]),FILTER(ALL('Table'),'Table'[NAME] = MAX(TASK[NAME])&&'Table'[TASK]=MAX(TASK[TASK])&&'Table'[SUB] = MAX(TASK[SUB])&&'Table'[Date]>=_START&&'Table'[Date]<=_END&&'Table'[Leave Flag]=BLANK()))
VAR _EFFORT = CALCULATE(SUM(TASK[EFFORT]),FILTER(ALL(TASK),TASK[NAME]=MAX('Table'[NAME])&&TASK[TASK]=MAX('Table'[TASK])&&TASK[SUB]=MAX('Table'[SUB])))
VAR _RESULT = IF(MAX('Table'[Date])>=_START&&MAX('Table'[Date])<=_END,IF(MAX('Table'[Leave Flag])=BLANK(),DIVIDE(_EFFORT,_DayDiff),"L"))
RETURN
_RESULT

Build a matrix and result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Gurukiran003]

tq🙂