Help with SamePeriodLast Year

[Anonymous]

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:

# 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]&" "&"-"))

So for # of Unique Customers = DISTINCTCOUNT(Customer[Customer ID] 

and for # of Unique Customers Calendar SPLY = CALCULATE([# of Total Unique Customers], SAMEPERIODLASTYEAR('Order Date'[Calendar Date]))

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. 

[v-frfei-msft · Accepted solution]

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]&" "&"-"))

[Ashish_Mathur]

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]))

[Sergiy]

=UNICHAR(11205) for "▲" 

=UNICHAR(11206) for "▼"

[Anonymous]

It's not the unicode. The error is referncing some contigous date in the same period calculation.