Need help with DAX

[Maheshguptaz]

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!

[v-yilong-msft]

Hi @Maheshguptaz ,

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.

[Maheshguptaz]

Hi @v-yilong-msft ,

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.

[v-yilong-msft · Accepted solution]

Hi @Maheshguptaz ,

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.

[Maheshguptaz]

Hello @v-yilong-msft ,

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

[miTutorials]

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 

[Maheshguptaz]

Hi,
I've already tried that but it's giving me an error every time I input the values in it.