Wrong Totals Using IF and Values()

[Derf95]

Hi, 

I am working on getting the amount of new sales and lost sales with regards to sales previous year and sales year to date. I am trying to show this in a table and then filtering that table with a year slicer. 

Below are the formulas that i have used: 

SalesPY = CALCULATE(SUM(SalesData[Value]),SAMEPERIODLASTYEAR('Calendar'[DateKey]))

SalesYTD = TOTALYTD(SUM(SalesData[Value]), 'Calendar'[DateKey])

NewSalesUppdate = SUMX(VALUES(SalesData[CustomerName]),IF([SalesYTD] > 0 && [SalesPY] = 0, [SalesYTD]))

LostSalesUppdate = SUMX(VALUES(SalesData[CustomerName]),IF([SalesYTD] = 0 && [SalesPY] > 0, -[SalesPY]))

LostSalesOld = IF([SalesPY] > 0 && [SalesYTD] = 0, -[SalesPY])

As you can see in the images below, the NewSalesUppdate formula works as it should and sums up correctly. However LostSalesUppdate does not work, deipite having pretty much the opposite formula compared with NewSalesUppdate. It seems like the IF statement never becomes true. That is strange because as you can see the LostSalesOld formula shows the right value, but it does not show the total. 

All tips are appreciated!

[v-ljerr-msft]

Hi @Derf95,

That's a little weird. The logic of your measures seems all right. Could you share a sample pbix file which can reproduce the issue? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Regards

[Derf95]

Hi, @v-ljerr-msft, 

Thanks for your reply!

Here is a link to one drive where i have posted the test file: https://1drv.ms/u/s!AtXhBVThw98Ja07SGd9hSLPNGxo 
There are a few extra measures in the file, but the ones that should be focused on are the ones that i mentioned in the first post. In the report view you may switch from year 2015 to 2016 to see how newSales/lostSales work/Dont work. If you have any tips on changes i can make in order for the lostSales to show and sum up the right values, that would be great. 

Best Regards

[Derf95]

After some more testing i have added the following formula to the table: 

CustomerNameCount = COUNTROWS(VALUES(SalesData[CustomerName]))

As you can see in the image below, it seems that the Values() method doesn´t work unless there exists a SalesYTD value. 

I just wanted to add that in case it can help someone figure out a solution.