YOY Total with IF Condition on customer value

[Anonymous]

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 Sales)

I have tried the following: 

value_last_FY = CALCULATE(SUM(Sales[Value]),SAMEPERIODLASTYEAR('Date'[Date]))

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

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

total_value works well when Year & Customer column is added in the table view, 

BUT, When I remove Customer column, the total value for each year does not sum up correctly 

Thank you in advacne

[amitchandak]

@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]

@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]

@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]

@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]

@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.

[amitchandak · Accepted solution]

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

I created a new table for customer