Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 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
    5 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
5 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
5 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
    5 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
      5 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?

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        amitchandak  I have uploaded a sample PBIX file. Can you please take a look .

        https://drive.google.com/drive/folders/1E_k7VbgImoTft43JOncSMrwrWtCwVUVx?usp=sharing

         

        The correct Estimate_Value measure should be total of tmp_value measure per year. (Total at year level is not correct).

        FY Year starts from 01st Sept. Below is the desired result .

         

        2018 - 18558377 

        2019 - 22658571

        2020 - 25084241

        2021 - 29162252

         

        What am I missing here ? Thanks a lot for your support.