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_Hours.I need help with getting a weekly rolling Avg per emp. Below is sample of the data and the outcome is mentioned as "Rolling Avg Working Time per week":EmpIDweek_KeyWorking hours on that weekAbsence hours on that weekFixed working time, from Maconomy timecardCount of week (1 if no absences)Rolling Average working time per week11300301.0030.001237.57.537.50.8037.501340037.51.0038.391430037.51.0036.18150037.51.0028.651637.5037.51.0030.171737.5037.51.0031.2528037.537.50.000.0021060037.51.0060.0021170037.51.0065.0021237.5037.51.0055.8321339037.51.0051.63214340301.0048.10 Any help on this would be appreciated!!!Thanks in advance!

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.

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_Hours.

I need help with getting a weekly rolling Avg per emp. Below is sample of the data and the outcome is mentioned as "Rolling Avg Working Time per week":

EmpID	week_Key	Working hours on that week	Absence hours on that week	Fixed working time, from Maconomy timecard	Count of week (1 if no absences)	Rolling Average working time per week
1	1	30	0	30	1.00	30.00
1	2	37.5	7.5	37.5	0.80	37.50
1	3	40	0	37.5	1.00	38.39
1	4	30	0	37.5	1.00	36.18
1	5	0	0	37.5	1.00	28.65
1	6	37.5	0	37.5	1.00	30.17
1	7	37.5	0	37.5	1.00	31.25
2	8	0	37.5	37.5	0.00	0.00
2	10	60	0	37.5	1.00	60.00
2	11	70	0	37.5	1.00	65.00
2	12	37.5	0	37.5	1.00	55.83
2	13	39	0	37.5	1.00	51.63
2	14	34	0	30	1.00	48.10

Any help on this would be appreciated!!!

Thanks in advance!

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

6 Replies

miTutorials
Super User
2 years ago
Have you explored the new Visual Calculations feature yet ? If not take a look at this video, this should solve your query.

🚀 NEW FEATURE : Magic of Visual Calculations in Power BI | MiTutorials - YouTube

Regards

Ismail
- Anonymous
  Not applicable
  2 years ago
  Hi,
  I've already tried that but it's giving me an error every time I input the values in it.

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

Forum Discussion

Need help with DAX