## Calculating the accumulative by week

Anything related to PowerPivot and DAX Formuale
The maintenance guy
Posts: 3
Joined: Sat Nov 16, 2019 8:04 pm

### Calculating the accumulative by week

Hello,

I have a measure that calculates how many times a worker was "shifted/Rotated" from one production line to another per week, but the results given to me are only correct if i chose to see the results by 1 week and not more, if i choose 2 weeks it runs the measure on the results of 2 weeks together so i get a wrong answer, what i need to have is a measure that adds up the "Rotation" result of each week to the other chosen week and so on!

Here is the measure:
Rotation = DISTINCTCOUNT('Table 1'[name of the production line])
/
VALUE(DISTINCTCOUNT('Table 1'[Worker name]))

As you can see the measure depends on the function "DISTINCTCOUNT" , and that is because lets say that worker "Mark" worked on 3 different production lines this week "Production lines 8,3,5,8" -yes they are 4 but two of them are the same "Line number8" so its not counted as a "Rotation"- so it means that he was rotated 3 times only this week, so the result of this measure above for this specific week well be "3" till now all is fine, so let´s assume that for the next week he was "Rotated" 4 times for lines(8,5,3,7) so the results should be 7 times Rotation if i choose showing me the results for those 2 weeks, but what happens is the function "DISTINCOUNT" well be running again on the total results of the 2 weeks excluding any repeated production line name showing me the final result as 2 "because lines 8,3,5 will be counted only once in the 2 weeks since i am using DISTINCOUNT", if i use "COUNT" it will only counts the number of production lines without eliminating the "repeated names".

Would appreciate your help on this.

Attachments
Correct result
ROTATION 2.jpg (57.1 KiB) Viewed 469 times
incorrect result
Rotation.jpg (69.43 KiB) Viewed 469 times

Bernard Heymans
Posts: 28
Joined: Wed Feb 28, 2018 12:18 am

### Re: Calculating the accumulative by week

Hi ,

Can you post a small set of data?
without example it is not easy to answer.

The maintenance guy
Posts: 3
Joined: Sat Nov 16, 2019 8:04 pm

### Re: Calculating the accumulative by week

Hello Bernard,

Here it is.

Attachments
sample.xlsx

Bernard Heymans
Posts: 28
Joined: Wed Feb 28, 2018 12:18 am

### Re: Calculating the accumulative by week

does this help?
Attachments
sample.xlsx

The maintenance guy
Posts: 3
Joined: Sat Nov 16, 2019 8:04 pm

### Re: Calculating the accumulative by week

Thank you, but unfortunately not