Calculating Average and using DateMonth

DAX DAX Calculations
1

Trying to summarize safety statistics from observations made:

An observation can be made by an individual and recorded with the date. More than one individual can enter an observation on same date and an individual can enter more than one observation each day.

I have a list of the records something like:

EEName, Date of Entry, # Observation, Month#

image
image.png1746×865 126 KB

Observation is always 1 as there is an entry for each record

I want to determine the average over a period of time.

I created a Date table spanning 1/1/2018 to 12/31/2019 and linked them by Date of Entry to Date in table.

I then want to have a table or chart with EE name and then the total observations as well as average observations per EE and each month as well as each year

I have used this:

Avg per DATE = SUM(Data[# Observations])/DISTINCTCOUNT('Date'[Date])

as well as

Avg per DATEday = SUM(Data[# Observations])/DISTINCTCOUNT('Date'[date].[Day])

First seems like total observations over complete time period in date table per EE
Second is confusing

Also can do ,

Avg per DATEmonth = SUM(Data[# Observations])/DISTINCTCOUNT('Date'[Month])

Avg per DATEyear = SUM(Data[# Observations])/DISTINCTCOUNT('Date'[Date].[Year])

what are the *.[Month] or *.[Day] doing differently?

Also when in a table ADDS THE AVERAGES

How can I find out averages for months submitted not over time period and what’s the difference with the *.[ ] parameters.

Pasting an excel table below. weird pipe character. How do you load a table?

|EE|Month#|# Observations|Date of Entry|

2

Ok you want to definitely be using AVERAGEX here instead of the logic you have created.

You need to make sure you have a good understanding of iterating functions because this is how you calculate any results like you need here.

See below.

https://blog.enterprisedna.co/2018/09/17/using-the-average-x-function/

The important concept here is what you want to iterate through in your iterating function.

Check out these examples to learn more about this.

http://portal.enterprisedna.co/courses/108877/lectures/2000574

Then when you want to calculate the total of averages you want to use a technique like the below.

See how you go with all these ideas. Once you understand how iterating functions works you’ll actually realize this isn’t too difficult.

Good luck.

Thanks
Sam