Calculate in DAX the average age per day and per month

[game1]

Calculate the average age per day and per month
For example:

Date         PersonAge

2020/01/01     2

2020/01/01     10

2020/01/02     5

2020/01/30     8

2021/01/01     2

2021/01/03    12

2021/02/03     2

2021/02/03     4

2021/02/03     3

Per day, we must obtain : 

2020/01/01 -> (2+10)/2 = 6

2020/01/02 -> 5

2020/01/30 -> 2

2021/01/03 ->12

2021/02/03  -> (2+4+3)/3 = 3

Per month, we must obtain : 

2020/01 -> (2+10+5+8)/4 = 6.25

2021/01 -> (2+12)/2 = 7

2021/02   -> (2+4+3)/3 = 3

[v-kpoloju-msft]

Hi @game1,
Thank you for reaching out to the Microsoft fabric community forum. Also, thanks to @FBergamaschi, @mdaatifraza5556, @rohit1991, for his inputs on this thread.

Has your issue been resolved? If the response provided by the community member @FBergamaschi, @mdaatifraza5556, @rohit1991, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[v-kpoloju-msft]

Hi @game1,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

[v-kpoloju-msft]

Hi @game1,

Just wanted to follow up one last time. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[FBergamaschi]

My code is the simplest among the different answers and it consists in a unique measure

Measure

Average AGE = AVERAGEX( 'Table', 'Table'[Person Age])

To see things by month, create a calculated column in the Table with the following code

YearMonth = YEAR ( 'Table'[Date] ) & FORMAT ( MONTH ( 'Table'[Date] ), "00" )

and use this column in the visual

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[mdaatifraza5556 · Accepted solution]

Hi @game1 

Please folow the below stpes to get your result.

1. Create a calulated column for year-month using below dax

                       YearMonth = FORMAT ( 'Table'[Date], "YYYY-MM" )

2.Create two measure using below dax.
 a. 

Avg Age Per Day =

AVERAGEX (

    VALUES ( 'Table'[Date] ),

    AVERAGE ( 'Table'[PersonAge] )

)

b.

Avg Age Per Month =

AVERAGEX (

    VALUES('Table'[YearMonth]),

    AVERAGE ( 'Table'[PersonAge] )

)

Also i have attached pbix file for your ref.

 If this answers your questions, kindly accept it as a solution and give kudos.

[rohit1991]

Hi @game1 

Below is the dax for average per day as well as month:

1) Average per Day

Avg per Day =
VAR d = MAX ( Facts[Date] )
RETURN
AVERAGEX ( FILTER ( Facts, Facts[Date] = d ), Facts[PersonAge] )

2) Average per Month (row-level average across the month)

Avg per Month =
VAR y = YEAR ( MAX ( Facts[Date] ) )
VAR m = MONTH( MAX ( Facts[Date] ) )
RETURN
AVERAGEX (
   FILTER ( Facts, YEAR ( Facts[Date] ) = y && MONTH ( Facts[Date] ) = m ),
   Facts[PersonAge]
)