Forum Discussion
Help with SamePeriodLast Year
I need help crafting this formula to throw the right UP and Down arrow based if the current # of Unique Customers is greater than the # of Unique Customers last year in any given same period (based off the calendar date)
Here is the formula that I need to work:
With all that fun stuff said I get a nasty error message saying "same period last year expects a contigous selection when the date column is not unique. It sees to break on the # of Unique Customers Calendar SPLY. SPLY stands for SAME PERIOD LAST YEAR.
Hi Anonymous ,
I think that should be the issue of your 'Order Date' table. Please create a CALENDAR table and create relationship between fact table and it. Then update your measure as below.
CALENDA= CALENDARAUTO()
# of Unique Customers Calendar SPLY = CALCULATE([# of Total Unique Customers], SAMEPERIODLASTYEAR('CALENDAR'[Date]))BTW, I created a sample for your reference.# of Unique Customers = SUM('Sales (2)'[Customer Price Amount EUR])# of Unique Customers Calendar SPLY = CALCULATE([# of Unique Customers],SAMEPERIODLASTYEAR('date'[Date]))# of Unique Customers KPI = IF([# of Unique Customers] > [# of Unique Customers Calendar SPLY], [# of Unique Customers]&" "&"▲", IF([# of Unique Customers] < [# of Unique Customers Calendar SPLY], [# of Unique Customers]&" "&"▼", [# of Unique Customers]&" "&"-"))
4 Replies
Hi,
There should ideally be a Calendar Table. Build a relationship from the Calendar Date column of the Order Date Table to the Date column of the Calendar Table. To your visual, drag the Year/Month/Date or any other date dimension from the Calendar Table. Write this measure
# of Unique Customers Calendar SPLY = CALCULATE([# of Total Unique Customers], SAMEPERIODLASTYEAR('Calendar'[Date]))
Hi Anonymous ,
I think that should be the issue of your 'Order Date' table. Please create a CALENDAR table and create relationship between fact table and it. Then update your measure as below.
CALENDA= CALENDARAUTO()
# of Unique Customers Calendar SPLY = CALCULATE([# of Total Unique Customers], SAMEPERIODLASTYEAR('CALENDAR'[Date]))BTW, I created a sample for your reference.# of Unique Customers = SUM('Sales (2)'[Customer Price Amount EUR])# of Unique Customers Calendar SPLY = CALCULATE([# of Unique Customers],SAMEPERIODLASTYEAR('date'[Date]))# of Unique Customers KPI = IF([# of Unique Customers] > [# of Unique Customers Calendar SPLY], [# of Unique Customers]&" "&"▲", IF([# of Unique Customers] < [# of Unique Customers Calendar SPLY], [# of Unique Customers]&" "&"▼", [# of Unique Customers]&" "&"-"))
=UNICHAR(11205) for "▲"
=UNICHAR(11206) for "▼"
