Forum Discussion
Calculating Average
I need to represent the average per year from the following. The values in the matrix are the Count of Distinct (Name). The dates are (Created Date). It's okay if it requires a separate table visual....
Hi,
Try the following DAX Code:
AveragePerYear =
VAR CurrentYear = YEAR(MAX('YourTable'[Month]))
VAR PreviousMonth = IF(MONTH(TODAY()) > 1, MONTH(TODAY()) - 1, 1)
VAR StartYear = IF(CurrentYear = 2023, 2017, 0)
VAR EndYear = IF(CurrentYear = 2023, 2022, CurrentYear)
VAR TotalMonths = IF(CurrentYear = 2023, PreviousMonth, 12)
RETURN
DIVIDE(
CALCULATE(DISTINCTCOUNT('YourTable'[Name]),
'YourTable'[Month] >= DATE(StartYear, 1, 1),
'YourTable'[Month] <= DATE(EndYear, TotalMonths, 1)
),
IF(CurrentYear = 2023, TotalMonths, 12)
)
Give it a Thumbs up if this solves your challenge.