Hello,
I'm trying to identify same store sales also known as "like for like" from my dataset.
I've read through every post previously about this topic, watched lots of youtube videos and read countless blog posts but I still can't seem to get it to function and it seems like there is something very basic happening incorrectly on my end.
In my fact table, NorAm I have many stores, with unique store ID's, the net sales for each of those stores for every month if there were any sales. In many cases some of these stores may have several months of zero sales for any given year.
I created a date table that has unique dates within it and it is linked to the NorAm table with a one to many relationship on the Noram[Date] and DateTbl[DateKey] fields.
When I create my initial measure to sum total sales it looks like this:
TotalNetSales =
Sum(NorAM[Net Sale])
When I create the next measure to identify prior year sales it looks like this:
TotalNetSalesLY =
CALCULATE([TotalNetSales],SAMEPERIODLASTYEAR(NorAM[Date]))
*Notes below about why I didn't use the Date Table below
The ouput into a table with the columns Date:TotalNetSales:TotalNetSalesLY looks like this:
9/1/17: 60,489
10/1/17: 65,231
11/1/17: 58,123
12/1/17: 57,312
1/1/18: 46,456
2/1/18: 59,560
3/1/18: 65,795
4/1/18: 67,600
5/1/18: 58,186
6/1/18: 51,984
7/1/18: 52,201
8/1/18: 61,500
9/1/18: 66,691: 60,489
10/1/18: 75,730: 65,231
The above output is what is expected as there is no data in the NorAm table prior to 9/1/17 and within this output I should expect to see the value for 9/1/17 along side of the value from 9/1/18.
My next step was to create a measurement that finds the difference between the variance between the two if there is prior year data:
TotalNetSalesVAR =
if(isblank ([TotalNetSalesLY]),Blank(),[TotalNetSales] - [TotalNetSalesLY])
The output into a table is as expected for the totals however as soon as I pull in store ID to show how the stores are performing individually I get error messages indicating that sameperiodlastyear is expecting a contiguous selection when the date column is not unique, has gaps or it contains time portion.
If I was working in excel I'd complete an index match to identify which stores had sales in which periods and then narrow the total population down to only those that had sales in the same months for each year. I'm trying to avoid my old ways of working but I can't shake the fact that it seems much easier to transform everything within excel before bringing it into BI.
Any help would be greatly appreciated.
*When I use the date table for my TotalNetSalesLY measurement it looks like this:
TotalNetSalesLY = CALCULATE([TotalNetSales],SAMEPERIODLASTYEAR(DateTbl[DateKey]))
The ouput into a table with the columns Date:TotalNetSales:TotalNetSalesLY looks like this:
9/1/17: 60,489: 60,489
10/1/17: 65,231: 65,231
11/1/17: 58,123: 58,123
12/1/17: 57,312: 57,312
1/1/18: 46,456: 46,456
2/1/18: 59,560: 59,560
3/1/18: 65,795: 65,795
4/1/18: 67,600: 67,600
5/1/18: 58,186: 58,186
6/1/18: 51,984: 51,984
7/1/18: 52,201: 52,201
8/1/18: 61,500: 61,500
9/1/18: 66,691: 66,691
10/1/18: 75,730: 75,730
So there is definitely something wrong with the link between the DateTbl and the fact table that needs to be addressed, but I dont know if fixing that will fix everything else.
