Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

YOY Total with IF Condition on customer value

Hi All,    I need to show the Total Value for each YEAR based on IF condition applied on the customer.    Example for 1 customer in current year: If (Total Sales = 0 , Last Year Sales, Total Sal...
Need Help
Show and Tell
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous , Please find the file. I have corrected tmp_value. Refer Page 4

    I created a new table for customer 

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous , you can have 

value_last_FY = CALCULATE(SUM(Sales[Value]),SAMEPERIODLASTYEAR('Date'[Date]))
or
value_last_FY = CALCULATE(SUM(Sales[Value]),dateadd('Date'[Date],-1,year))

 

use isblank

tmp_value = IF( isblank(SUM(Sales[Value])) , [value_last_FY] , SUM(Sales[Value]))

 

tmp_value = IF( isblank(SUM(Sales[Value])) || SUM(Sales[Value]) =0  , [value_last_FY] , SUM(Sales[Value]))

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    amitchandak :  Thanks for your response.


    Please see the screen shot.  total_value measure works well with Year ->   Customer -> total_value 

    BUT, on the aggregated view the Total does not sum up correctly

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

      Anonymous , you have try like

       

      total_value = SUMX(values('Date'[Date]), [tmp_value ])

       

      total_value = SUMX(values('Date'[Year]), [tmp_value ]) // on second one if first one does work there

       

      That is the row context. So the grand total is calculated again.  This will force it take from row totals

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        amitchandak : Somehow it is not working, but I think of the following issue. 

         

        In IF measure , I would like to consider prevoius year value of the same customer , ONLY when customer has NO order in current FULL  year. 

        In the current scenario, it is picking prevoius year value based on the Date[Date]. It seems like something to do with SUMMARIZE 

         

        Any Idea?