Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])))
Solved! Go to Solution.
Hi,
Try this
Total Sales = SUM('2018 DSR'[TotalSale])
Total Sales SPLY = CALCULATE([Total Sales],SAMEPERIODLASTYEAR(Calendar[Date]))
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
hi, @RAdams
It's pleasant that your problem has been solved, could you please mark the reply as Answered?
Best Regards,
Lin
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |