Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

blank keyword

i tried this expression for last year sales but this shows BLANK instead of 0 .. how to show 0 when there is no amount/data

 

expression

last_year_sales = var current_year= YEAR(MAX(Data[Date Main])) return  CALCULATE(SUM(Data[Net Sales]) ,(FILTER(Data,Data[Year]=current_year -1))
)
Need Help
Show and Tell
  • lukiz84's avatar
    lukiz84
    3 years ago

    just add a "+0" at the end of your CALCULATE()

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Anonymous 

    last_year_sales =
VAR current_year =
    YEAR ( MAX ( Data[Date Main] ) )

VAR previous_year = 
    CALCULATE (
        SUM ( Data[Net Sales] ),
        (
            FILTER ( Data, Data[Year] = current_year - 1 )
        )
    )

RETURN

if(previous_year = "", 0, previous_year)


    Try this


    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Anonymous this shows an error 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Anonymous 

        last_year_sales =
VAR current_year =
    YEAR ( MAX ( Data[Date Main] ) )

VAR previous_year = 
    CALCULATE (
        SUM ( Data[Net Sales] ),
        (
            FILTER ( Data, Data[Year] = current_year - 1 )
        )
    )

RETURN

if(previous_year <= 0, 0, previous_year)


        Try this instead

    • lukiz84's avatar
      lukiz84
      Icon for Memorable Member rankMemorable Member
      3 years ago

      This can't work because previous_year is a number. You can't compare a number to a string. You could only compare it to blank

       

      IF(ISBLANK(previous_year), 0, previous_year)

  • lukiz84's avatar
    lukiz84
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Hi,

     

    last_year_sales =
   var current_year= YEAR(MAX(Data[Date Main]))
   return
      CALCULATE(
         SUM(Data[Net Sales]), 
         (FILTER(Data,Data[Year]=current_year -1))
      )+0
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      lukiz84 this shows an error also 

       

      • lukiz84's avatar
        lukiz84
        Icon for Memorable Member rankMemorable Member
        3 years ago

        just add a "+0" at the end of your CALCULATE()