Calculate average but ignoring when only one row

[Pier2]

Hello,

I do calculate an average for a period of time based on

I want to stop calculating an average when only one value is available, which is usually the first year of the period because there's nothing before.

Exemple

Year	Values	Actual Mean returned	Real Mean wished
2014	18	18
2015	20	19	19
2016	62	41	41
2017	35	48,5	48,5

I've try different approches and it simply move foward the problem in the period.

Any idea?

[Ashish_Mathur]

Hi,

Assuming you have a measure by the name of [Actual in previous year], write this measure

Measure = if(isblank([actual in previous year]),blank(),averagex(summarize(calculatetable(calendar,previousyear(calendar[date])),calendar[year],"A",[amount]),[A]))

Hope this helps.

[DataNinja777 · Accepted solution]

Hi @Pier2 ,

You can achive your required output in many different ways and one of them is creating a calculated column as follows:

Previous year =
VAR PY =
    CALCULATE (
        SUM ( 'Table'[Values] ),
        FILTER ( 'Table', 'Table'[year] = EARLIER ( 'Table'[Year] ) - 1 )
    )
VAR CY =
    CALCULATE ( SUM ( 'Table'[Values] ) )
RETURN
    IF ( PY = BLANK (), BLANK (), ( PY + CY ) / 2 )

I attach an example pbix file.  

[Pier2]

Thanks you for your help @Ashish_Mathur  and @DataNinja777 . Problem solved.