CODEWITHSUNDEEP
powerbidax
Qianru Song
Qianru Song

Reputation: 331

PowerBI DAX Calculation: moving average group calculation by month

I have a table, and I want to calculate the average sales based on a country and a channel but the average moves by monthly: Sales by Country, Channel and Period

So for the first Average Sales only considers 202001, then the second one will for 202001 and 202002, and so on. The final output is Average Sales. Here sales is a measure created. Now I have the period until 202003, later I may have April, May, etc.My question is how to calculate Average Sales. Thank you.

Upvotes: 0

Views: 1048

Answers (1)

Agustin Palacios
Agustin Palacios

Reputation: 1206

Create a calculated column and use the following DAX formula: 

Average Sales = 
VAR __country = 'Table'[Country]
VAR __period = 'Table'[Period]
VAR __subTable = FILTER( 'Table', 'Table'[Country] = __country && 'Table'[Period] < __period + 1)

Return CALCULATE(  AVERAGE('Table'[Sales], __subTable )

Hope it helps you.

Upvotes: 0

Related Questions