2017 SAMEPERIODLASTYEAR VS 2018 Not working properly in 2019

[RAdams]

I have a report that uses SAMEPERIODLASTYEAR to sum up 2017 sales so I can compare to 2018 Sales Data but when it turned 2019, it stopped working properly. FYI, my Calendar[Date] table is 1/1/17 - 12/31/18

I have a table '2018 DSR' that contains 2017 and 2018 Sales Data. 

So I use this formula to pull 2018 sales with some isblank error handling.

2018 $ = IF(ISBLANK(SUM('2018 DSR'[TotalSale])),BLANK(),SUM('2018 DSR'[TotalSale]))

Now I use this formula to SUM up my 2017 data with some isblank handling. 

2017 $ = 
IF(ISBLANK(CALCULATE(SUM('2018 DSR'[TotalSale]),
SAMEPERIODLASTYEAR('Calendar'[Date]))),
BLANK(),
CALCULATE(SUM('2018 DSR'[TotalSale]),
SAMEPERIODLASTYEAR('Calendar'[Date])))

So what's happening is 2018 $ is totalling up BOTH 2017 and 2018 Sales. 2017 $ sales are good. 

I do have a page filtering on to filter 2018. I even tried turning this off, only 2017, only

I have tried filtering the 2018 $ sales to only include 2018 data using various techniques to no avail. 

Has anyone come across this? 

R

[Ashish_Mathur]

Hi,

Try this

1.  In the Calendar Table, write these calculated column formulas: Year = YEAR(Calendar[Date]) and Month = FORMAT(Calendar[Date],"mmmm")
2.  Ensure there is a relationship from the Date column of your 2018 DSR table to the Date column of the Calendar Table
3.  Create slicers for Year and Months from the calendar Table.  In the Year slicer, select 2018 and select any one month
4.  Write these measures

Total Sales = SUM('2018 DSR'[TotalSale])

Total Sales SPLY = CALCULATE([Total Sales],SAMEPERIODLASTYEAR(Calendar[Date]))

[v-lili6-msft]

hi, @RAdams

I have tested by your formula, It works well.

For example:

2017 total sales is 78, and 2018 sales is 222.

And do you mean that 2018 $ only shows 222, should not show 78?

and I think your two measure name should be 

current year $ = IF(ISBLANK(SUM('2018 DSR'[TotalSale])),BLANK(),SUM('2018 DSR'[TotalSale]))

and 

Last year $ = 
IF(ISBLANK(CALCULATE(SUM('2018 DSR'[TotalSale]),
SAMEPERIODLASTYEAR('Calendar'[Date]))),
BLANK(),
CALCULATE(SUM('2018 DSR'[TotalSale]),
SAMEPERIODLASTYEAR('Calendar'[Date])))

And they need to be used with date table in the visual

SAMEPERIODLASTYEAR is a time-intelligence-function, It needs a date table

https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax

https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

And If you don't want to use date table, please use these formula as a reference:

2018 $= IF(ISBLANK(CALCULATE(SUM('2018 DSR'[TotalSale]),FILTER('2018 DSR',YEAR('2018 DSR'[Date])=2018))),BLANK(),CALCULATE(SUM('2018 DSR'[TotalSale]),FILTER('2018 DSR',YEAR('2018 DSR'[Date])=2018)))

2017 $= IF(ISBLANK(CALCULATE(SUM('2018 DSR'[TotalSale]),FILTER('2018 DSR',YEAR('2018 DSR'[Date])=2017))),BLANK(),CALCULATE(SUM('2018 DSR'[TotalSale]),FILTER('2018 DSR',YEAR('2018 DSR'[Date])=2017)))

Best Regards,

Lin

[RAdams · Accepted solution]

Thanks for taking the time answer. I double checked my code and it's working now! Thanks for your help. R

[v-lili6-msft]

hi, @RAdams

It's pleasant that your problem has been solved, could you please mark the reply as Answered?

Best Regards,

Lin

[RAdams]

I've been trying to Accept the Solution but when I click on the Buttons, I get this message: Authentication failed for the action you are trying to do. This failure could be due to your browser not supporting JavaScript, JavaScript not being enabled, or trying to use the action URL directly in the browser address bar instead of clicking the link on the page. Not sure if it's just me or is anyone else seeing this? I'm able to respond but I can't Accept Solutions or give Kudos for that matter. R

[v-lili6-msft]

hi, @RAdams

It seems that there is something wrong with the browser for BBS, some customers have the same problem with you, You could try to change the browser like IE ,ect. then it will works welll.

Hope this will help you.

Best Regards,
Lin