Page 1 of 1

Calculating the accumulative by week

Posted: Sun Nov 24, 2019 11:58 pm
by The maintenance guy
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.

Many Thanks in Advance.

Re: Calculating the accumulative by week

Posted: Mon Nov 25, 2019 9:14 pm
by Bernard Heymans
Hi ,

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

Re: Calculating the accumulative by week

Posted: Sun Dec 01, 2019 5:54 pm
by The maintenance guy
Hello Bernard,

Here it is.

Mnay Thanks in Advance.

Re: Calculating the accumulative by week

Posted: Mon Dec 02, 2019 11:45 pm
by Bernard Heymans
does this help?

Re: Calculating the accumulative by week

Posted: Fri Dec 06, 2019 6:07 am
by The maintenance guy
Thank you, but unfortunately not