topic Re: Average Records Per Month Excluding the Unfiltered Maximum Month and Year in DAX Commands and Tips

Average Records Per Month Excluding the Unfiltered Maximum Month and Year

Topjacket — Thu, 11 Aug 2022 18:39:13 GMT

Hi All

I'm hoping this is a really easy one but as usual its driving me mad!!

I have written the following measure which is working fine until I apply a slicer. I need to return the average number of records excluding the latest month and year in the entire data (the reson being the latest month and year is never fully populated). The only way I could think to do it is to "distinct count" the number of "month and year" and then minus 1, however, if I put a slicer to exclude the latest month and year I am then unnecessarily reducing the number of dates by one and therefore skewing the average.

Average Monthly Cases = Calculate(count(Data1[Number]),Data1[CreatedMonthAndYear] <= edate(max(Data1[CreatedMonthAndYear]),-1)) / (DISTINCTCOUNT(Data1[CreatedMonthAndYear])-1)

I think I need to do something along the lines of exclude the "Ignore filter" MAX but I can't do it without getting an error.

Any help would be great.

Thanks

James

Re: Average Records Per Month Excluding the Unfiltered Maximum Month and Year

johnt75 — Fri, 12 Aug 2022 10:30:57 GMT

If CreatedMonthAndYear is a numeric column in YYYYMM format then I think the below should work

Average monthly cases = VAR maxDate = CALCULATE ( MAX ( 'Data1'[CreatedMonthAndYear] ), ALL ( 'Data1'[CreatedMonthAndYear] ) ) RETURN AVERAGEX ( CALCULATETABLE ( VALUES ( 'Data1'[CreatedMonth] ), 'Data1'[CreatedMonthAndYear] < maxDate ), COUNTROWS ( 'Data1' ) )

Re: Average Records Per Month Excluding the Unfiltered Maximum Month and Year

Topjacket — Thu, 18 Aug 2022 22:53:15 GMT

Hi Johnt75

Thanks for the reply and sorry for the slow response. I did try this but it was just returning a count of all data. I'm sure it was something I was doing wrong but unfortunately I had to complete this work last week so didn't manage to go any further with it.

Thanks again

James