Forum Discussion

Anonymous

Not applicable

2 years ago

Solved

Need help with DAX

Hi, I'm trying to write a DAX query for weekly rolling avg per emp, however, I'm unable to get the desired results in my report. I've got Week_Key, Date_Key, Emp_ID, ActualWorkingHours, Avg_Ho...

Need Help

Anonymous

2 years ago

Hi Anonymous ,

I read your requirements and I do some changes with my DAX code.

Column =
VAR _A =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[EmpID] = EARLIER ( 'Table'[EmpID] )
                && 'Table'[week_Key] <= EARLIER ( 'Table'[week_Key] )
        ),
        'Table'[Working hours on that week]
    )
        / SUMX (
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[EmpID] = EARLIER ( 'Table'[EmpID] )
                    && 'Table'[week_Key] <= EARLIER ( 'Table'[week_Key] )
            ),
            'Table'[Count of week (1 if no absences)]
        )
RETURN
    IF ( 'Table'[Count of week (1 if no absences)] = 0, 0, _A )

Then you will get what you want.

Best Regards

Yilong Zhou

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

help with DAX (1).pbix27 KB

Anonymous

Not applicable

2 years ago

Hi Anonymous ,

Based on your problems, here are my answers.

I write a DAX query for weekly rolling average per employee.

Rolling Avg Working Time per week =
VAR CurrentEmpID =
    MAX ( 'Table'[EmpID] )
VAR CurrentWeekKey =
    MAX ( 'Table'[week_Key] )
VAR PreviousWeeks =
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[EmpID] = CurrentEmpID
            && 'Table'[week_Key] <= CurrentWeekKey
    )
VAR Result =
    AVERAGEX ( PreviousWeeks, 'Table'[Working hours on that week] )
RETURN
    Result

Then I get the result.

But I don't get the result you need, can you give me the specific Rolling Average working time per week calculation process or give me the corresponding .pbix file?

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yilong Zhou

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

help with DAX.pbix26 KB

Anonymous

Not applicable

2 years ago

Hi Anonymous ,

I'm unable to share the .pbix as there're certain restrictions for file sharing in my org.

This solution isn't working for me as I have got the information in three different tables namely "DimDate", "DimEmployee" and "FactProjectHours".

The fields that I'm considering for this requirement are:

TableName	FieldName
DimEmployee	EmpID
DimEmployee	EmpName
DimEmployee	FixedHours per Week
DimDate	YearKey
DimDate	WeekKey
FactProjectHours	WorkingHours on the given Week
FactProjectHours	AbsenceHouse

WeekCount ( (FixedHours Per Week - AbsenceHouse) / FixedHours Per Week) ----- This is a measure to count the week as shown in the previous data sample table.

Please refer to the below screenshot for the expected result of rolling avg which is calculated in Excel.

Anonymous
Not applicable
2 years ago
Hi Anonymous ,

I read your requirements and I do some changes with my DAX code.

Column = VAR _A = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[EmpID] = EARLIER ( 'Table'[EmpID] ) && 'Table'[week_Key] <= EARLIER ( 'Table'[week_Key] ) ), 'Table'[Working hours on that week] ) / SUMX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[EmpID] = EARLIER ( 'Table'[EmpID] ) && 'Table'[week_Key] <= EARLIER ( 'Table'[week_Key] ) ), 'Table'[Count of week (1 if no absences)] ) RETURN IF ( 'Table'[Count of week (1 if no absences)] = 0, 0, _A )

Then you will get what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
help with DAX (1).pbix27 KB
- Anonymous
  Not applicable
  2 years ago
  Hello Anonymous ,
  
  I don't think this DAX will work as the Week_key comes from DimDate, EmpID comes from DimEmp and WorkingHours, absenceHours and FixedWorkingTime come from FactTable.
  
  Could you help me with DAX according to the above conditions?
  
  regards,
  Mahesh