Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Not applicable

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




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

I created a new table for customer 

View solution in original post

Super User
Super User

@Anonymous , you can have 

value_last_FY = CALCULATE(SUM(Sales[Value]),SAMEPERIODLASTYEAR('Date'[Date]))
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]))

Not applicable

@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


@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

Not applicable

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



Not applicable

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


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.



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

I created a new table for customer 

Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.