Average of measures ( between 2 dates)

[Anonymous]

Raw data: Machine | Date | Unit | Week 

Calcaulations (What i need help with):

 Average = 53 = 2565/ 48 

        2565 = sum of units

            48 = sum # of active weeks. 

Sum (Active week for each machine) = last week is nonblank - first week is nonblank

Questions: How do i get to the overall level of #active week? (i can do Max(date) - Min(date) for individual machines, but for overall level, it will always be 6 weeks.. Average = 53 is what i need to get to. 

Thank you!

[v-lionel-msft]

Hi @Anonymous ,

Shouldn't it be like this?

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous ,

distinctcount([week number])

New column = [Machine] & [week Number]

distinctcount([New column])

[Anonymous]

@amitchandak It wont work that way, because its counting the week that has positive number.

So if week 2 and 4 has non 0, that will be counted as 2 week, but I need to count 2, 3, and 4. for 3 weeks. for each machines, 

then sum that result for each individual machine. like the following. 

[v-lionel-msft]

Hi @Anonymous ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-lionel-msft That's not what I am looking for. 

By the example in your file, the average will be 1010/26 weeks = 39/weeks

	#Week Counts
Machine1	5
Machine2	5
Machine3	3
Machine4	2
Machine5	4
Machine6	3
Machine8	2
Machine9	2
	26
	38.84615

[Fowmy]

@Anonymous 

The data you posted is in picture, just pate your data from Excel so I can solve and share the solution.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn