Divide column values every week

[harmonvi]

I need to divide weekly productivity per employee, by weekly hours per employee, repeating every week for the year. I need to keep every week's data so that I can track historical data.

Is there a way I can set this up to do it automatically or do I need to manually do it for every week? 

Productivity

This image shows the employee productivity per week

Hours

This image shows the employee hours worked each week 

The lower image shows the number of hours worked in a weeks time frame. The top image shows the employees productivity per week. I currenly have been doing the manual calculation every week -

7/4/2020 Avg Daily Productivity = Hours[7/4/2020] / (Producitvity [7/4/2020] / 8).

 I divide by 8 to get the daily instead of weekly average. This is of course combined with a lookup between employee names to pull the correct value.

[v-alq-msft · Accepted solution]

Hi, @harmonvi 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Productivity:

Hours:

Then you need to make the week columns selected and unpivot them in Power Query.

Then you may create a measure as below.

Result = 
DIVIDE(
    DIVIDE(
        SUM(Hours[Value]),
        CALCULATE(
            SUM(Productivity[Value]),
            FILTER(
                ALL(Productivity),
                [Name] in DISTINCT(Hours[Name])&&
                [Week] in DISTINCT(Hours[Week])
            )
        ),0
    ),8
)

Result:

Best Regards

Allan

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[edhans]

@harmonvi if you could provided data per the links below that would be helpful. I think you are going about this the wrong way. Rather than adding more and more columns like you would do in Excel, I think I would approach this with:

1. unpivoting the data in the date columns and creating a normalized table
2. Have a good date table (linked to below) that would have the weekly dates marked
3. Summarize and analyze your data in DAX using the Power BI model.

Right tool for the job - Power Query to shape data, DAX to analyze.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

Creating a Dynamic Date Table in Power Query