Year slicer changes card values based on prior years average

[Antonio195754]

Hi all, I have a date column with years 2018-2021.  Have been asked to use previous years average dollar amount in 2020, and return a count of "transactions" over that average that have occured YTD in 2021, in a card.  Per the table below, I created a DAX measure basically saying if >= 225,000 return 1 else 0.  So that works well when i'm comparing 2021 to the 2020 average, but is there a way that as you change the year slicer from say 2021 to 2020, that it dynamically changes to the prior years average amount of in this case, 2019's average of $94,000. Same for comparing 2019 to 2018 etc

Year	Average amount
2018	$118,000
2019	$94,000
2020	$225,000

[amitchandak]

@Antonio195754 , You need to have a separate year or date table. then you can have measures like 

This Year = CALCULATE(sum("order"[Qty]),filter(ALL("Date"),"Date"[Year]=max("Date"[Year])))
Last Year = CALCULATE(sum("order"[Qty]),filter(ALL("Date"),"Date"[Year]=max("Date"[Year])-1))

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

in case you have date 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD("Date"[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd("Date"[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR("Date"[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd("Date"[Date],-1,Year)),"12/31"))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Antonio195754]

@amitchandak , Thanks for your reply!  I created a date table like below:  

If i setup your recommended measures and join my created date table to the date column in my main data table, how will it know to use the previous years amount average when i change years in the Year slicer?  Also, as far as my average dollar amount per year i don't actually have a table for it (i have a year column and and amount column and i've just got the average by doing the math manually), i just use the average result from the given year in my DAX.

Date Table =

VAR BaseCalendar =

CALENDAR(DATE(2018,1,1),DATE(2021,12,31))

RETURN

GENERATE(

BaseCalendar,

VAR BaseDate = [Date]

VAR YearDate = YEAR (BaseDate)

VAR MonthNumber = MONTH (BaseDate)

RETURN ROW (

"DAY", BaseDate,

"YEAR", YearDate,

"MONTH NUMBER", MonthNumber,

"MONTH", FORMAT(BaseDate, "MMM"),

"YEAR MONTH", FORMAT(BaseDate, "YYYY-MM")))