Return previous and current year value and calculate the average

[ybyb23]

Hi all, 

I have created a dax function to return the avg of the current year and the one from previous year. I would like to add another line by saying if we have both years value, take the value of previous + the current /devide by number of years. However Iam facing issues with how to formulate it. Hereafter the sample of the dax, hope it makes sense to do it in this way.

Thank you! 

AVC =

VAR ThisYear = MAX(GH[calyear])

VAR var_current_year = YEAR(ThisYear)

Var var_previous_year = var_current_year -1

var var_value_current_year =

       CALCULATE(GH[Average],

       Filter(ALL(GH[calyear]),

       GH[calyear] = var_current_year )

                           )

var var_value_previous_year =

CALCULATE(GH[Average],

Filter(ALL(GH[calyear]),

GH[calyear] = var_previous_year )

                   )

Return

If (var_value_current_year || var_previous_year, GH[Average](current) + GH[Average](previous)   / DISTINCTCOUNT(GH[calyear]) )

[tamerj1]

Hi @ybyb23 
Please try

AVC =
VAR ThisYear =
    MAX ( GH[calyear] )
VAR var_current_year =
    YEAR ( ThisYear )
VAR var_previous_year = var_current_year - 1
VAR var_value_current_year =
    CALCULATE (
        GH[Average],
        FILTER ( ALL ( GH[calyear] ), GH[calyear] = var_current_year )
    )
VAR var_value_previous_year =
    CALCULATE (
        GH[Average],
        FILTER ( ALL ( GH[calyear] ), GH[calyear] = var_previous_year )
    )
VAR NumberOfYears =
    DISTINCT (
        FILTER (
            ALL ( GH[calyear] ),
            GH[calyear] IN { var_current_year, var_previous_year }
        )
    )
RETURN
    DIVIDE ( var_value_current_year + var_previous_year, NumberOfYears )

[ybyb23]

Hi @tamerj1, 

thank you for the solution. It seems not working I have basically bar chart, where I am supposed to highlight this total average. but it's empty currently no values highlited. 

[tamerj1]

@ybyb23 
Please provide some screenshots for better understanding.

[amitchandak · Accepted solution]

@ybyb23 , You should always use date or separate year tbale for that

example

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

with date you can also use the trailing year

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[ybyb23]

Hi @amitchandak , 

thank you for your solution, it worked perfetly!!. Currently I am trying to summerize it in one key measure, I am not sure if it's possible.