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