Forum Discussion

skorpion's avatar
skorpion
Icon for Helper I rankHelper I
8 years ago
Solved

Filter Formula

im trying to get the last year value and im getting blank output  im using this formula    Last Year Total = CALCULATE(SUM(Query1[Total Sale]),IF(Query1[Year] = 2016,""))     please help me out...
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago
    ast Year Total = CALCULATE(SUM(Query1[Total Sale]),Query1[Year] = 2016)
  • McCow's avatar
    McCow
    8 years ago

    Hi skorpion

    try this:

     

    Last Year Total = CALCULATE(SUMX(Query1;Query1[Total Sale]);FILTER(Query1;Query1[Year] = 2016))

    or without extra column [Year]
    Last Year Total = CALCULATE(SUM(Query1[Total Sale]);FILTER(Query1;YEAR(Query1[Dates]) = 2016))

    Best regs

McCow's avatar
McCow
Icon for Resolver III rankResolver III
8 years ago

Hi skorpion

try this:

 

Last Year Total = CALCULATE(SUMX(Query1;Query1[Total Sale]);FILTER(Query1;Query1[Year] = 2016))

or without extra column [Year]
Last Year Total = CALCULATE(SUM(Query1[Total Sale]);FILTER(Query1;YEAR(Query1[Dates]) = 2016))

Best regs

  • skorpion's avatar
    skorpion
    Icon for Helper I rankHelper I
    8 years ago

    I dont have dates Columns just have month and year column

  • McCow's avatar
    McCow
    Icon for Resolver III rankResolver III
    8 years ago



    McCow wrote:

     

     

    Last Year Total = CALCULATE(SUMX(Query1;Query1[Total Sale]);FILTER(Query1;Query1[Year] = 2016))

     

    this formula must be relevant for you. Or not?

    • skorpion's avatar
      skorpion
      Icon for Helper I rankHelper I
      8 years ago

      yes thanks for you help