Forum Discussion

Antonio195754's avatar
Antonio195754
Icon for Helper IV rankHelper IV
5 years ago

Year slicer changes card values based on prior years average

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

 

 

YearAverage amount
2018$118,000
2019$94,000
2020$225,000
Need Help
Show and Tell

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Antonio195754
      Icon for Helper IV rankHelper IV
      5 years ago

       

       

       

      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")))