Find Average for desired period

[Wroclaw]

Hi Everyone,

I have a dataset like this:

And I want to find an avarage amount per month, but only including months from first non-blank month to last non-blank month (in my example 202204 - 202305), so the outcome will be like this:

any idea for DAX formula which can achieve this? Thanks, Wroclaw

[ryan_mayu · Accepted solution]

@Wroclaw 

you can try to create a column

Column =

VAR _start=minx(FILTER('Table',not(ISBLANK('Table'[Amount]))),'Table'[YM])

var _end=maxx(FILTER('Table',not(ISBLANK('Table'[Amount]))),'Table'[YM])

return if('Table'[YM]>=_start&&'Table'[YM]<=_end,sum('Table'[Amount])/CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[YM]>=_start&&'Table'[YM]<=_end)))