Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

need help calculating yoy difference

I'm trying to not show the diff YOY actual for dates in the future, I tried a if statement but this does not apply to the totals so that it is still calculating across the row.  i'd like to sum just the difference to date.
 
diff YOY Acctual = if(isblank([Pipeline]),"",([Pipeline]-[Pipeline ly]))
 

 

 

Need Help

3 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Rows from 2021 Sept are forced to shown because of [PIPELINE LY]; what's more, "" is also a non-blank value to show.

    diff YOY Acctual =
VAR __p = [Pipeline] RETURN IF( NOT ISBLANK( __p ), ( __p - [Pipeline ly] ) )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      i'm still showing the incorrec sum on that column, not sure why this is happining, it blanks the data correctly but it is still summing across ther row.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      I have solved the issue with a different method, however now I have a different problem,

       

      I created some dummy data to work on the code,

       

      diff YOY amount = SUMX(
      SUMMARIZE('calendar','calendar'[Year],'calendar'[Month Name],"ABCD",
      if(ISBLANK([revneue]),
      BLANK(),
      [revneue]-[revenue ly])),
      [ABCD])

       

      as you can see the YOY is calculated correctly now at the year month level, but it is not correct at the customer level.  I need a generic YOY difference measure that can calculate YOY difference in by any column.  

       

      https://drive.google.com/file/d/11iIHWdMjnCOGJOmBBiIjFH2ljTKJ6vN3/view?usp=sharing