Cumulative average grain

Hi Dicken​,

I think the main thing here is to count only the months where you actually have data, rather than simply counting the months in the calendar.

You could calculate the cumulative units and then divide by the number of months that have a value.

AccAvg :=
VAR CurrentDate =
MAX ( 'Calendar'[Date] )
VAR CumUnits =
CALCULATE (
[Tunits],
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Date] <= CurrentDate
&& YEAR ( 'Calendar'[Date] ) = YEAR ( CurrentDate )
)
)
VAR MonthCount =
CALCULATE (
DISTINCTCOUNT ( 'Calendar'[MMM-YYYY] ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Date] <= CurrentDate
&& YEAR ( 'Calendar'[Date] ) = YEAR ( CurrentDate )
&& CALCULATE ( [Tunits] ) <> BLANK ()
)
)
RETURN
DIVIDE ( CumUnits, MonthCount )

For example, if you have:

Jan = 10
Feb = 20
Mar = 20
Apr = blank
May = 30

the result would be:

Jan → 10
Feb → 15
Mar → 16.67
Apr → 16.67
May → 20

So April doesn't increase the denominator because there is no data for that month.

I would also recommend having a proper month-level column in the Calendar table rather than using MMM-YYYY as the calculation grain. Something like MonthStart = DATE(YEAR('Calendar'[Date]), MONTH('Calendar'[Date]), 1) would be safer, particularly when you're working across multiple years.

Hi,

Try this measure

=AVERAGEX(datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])),[Tunits])

Hope this helps.

Hi Dicken​ 

AVERAGEX skips rows without data so they're not counted in the denominator. And if you want that average to be applied to the  whole visible rows, you can just use ALLSELECTED

Average without Jan = AVERAGEX(     ALL(Dates[Month and Year]),     [Total Revenue without Jan] )

 Average without Jan - allselected = CALCULATE(     AVERAGEX(         ALL(Dates[Month and Year]),         [Total Revenue without Jan]     ),     ALLSELECTED(Dates) )

Please see the attached pbix.

AccAvg = AVERAGEX( FILTER( VALUES('Calendar'[MMM-YYYY]), NOT ISBLANK([Tunits]) ), [Tunits] ) This averages only the months present in the current filter context with data, so missing months aren't assumed to exist.

Dicken​  if   month 1 = 10   and month 2 = 20   averate so far = 30  why here the average is 30?

could you pls provide some sample data and expected output?

Surry it should be 15, BTW has this site become very unstable over the last week,
i have difficulty using it 

Hi Dicken​ ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

Regards,

Chaithanya

Hi Dicken​ ,

The Community UI has recently been updated. For your reference, I am sharing a screenshot highlighting the option to accept the relevant answer that helped resolve your issue.

Thanks,

Chaithanya.