Forum Discussion
Total Sales Last Year , Need Help!!
Im on a project to get rid of Tableau.
I am having some issues with reporting on a value and the value in the same period last year.
Im aware of the SAMEPERIODLASTYEAR function, my issue is that when there is a store that did not have data for the whole preiod this year, it only shows the data for the same period last year for the days that had a value, NOT the whole period?
Attached is a screenshot showing the values for 01-Aug-2017 to 31-Aug-2017 as well as next to it 01-Aug-2018 to 31-Aug-2018
and then an image of the calculation with the sameperiodlastyear calc included.
It shows that it works as long as the site was there and had sales for the whole period.
It ignores if the site is no longer around (No sales in the Current Period), but i want it included.
I can provide my pbix (Not company confidential info, all values altered) if it will help someone.
3 Replies
This may cause more of a problem then a help but I have noticed that sometimes if you can get your data to show 0 instead of blank formulas have a way of behving differently.
New Sales = If(SUM(Sales)=0,"0",SUM(Sales))
or
New Sales = If(SUM(Sales)=BLANK(),"0",SUM(Sales))
depending on how the data is working
It would force data where there is none. Just an idea but I have not tested this.
Nah no good, the issue is that for the new time period there isnt data thats zero, there is just NO data.
E.G. the data looks like
01-Aug-2018 | 75
02-Aug-2018 | 150
03-Aug-2018 | 65
04-Aug-2018 | 48
END
But if i set the range to 2017 there is data for the whole month last year, but the powerbi table is only calculating it for the 01 to the 04 august in the above example
The saddest thing is, i can make EXACTLY what i need in a Stored Procedure in SQL, but then it would need to be run every time for every different month range.
