Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 o...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

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

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

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