cross tab output with calculated measure using sameperiod last year

[Anonymous]

hello,

newb here so i am probably missing some terminology that would make finding the answer to this much easier (apologies up front)

i have a report i am trying to create with rows and columns of calculated counts.

each row should have 4 columns. currently i have created a new measure for each column. so it should look like

Row 1: first-measure name: 

(Column 1) distinct Count 

(Column 2) distinct count using sameperiodlastyear    

(Column 3) the variance as first measure - second measure   

 (Column 4) the trend first measure / second measure

these measures seem to work when i put them in a bar chart etc unless... i try to show them per month. once i add the YEAR_MONTH column from our date dimension the SAMEPERIODLASTYEAR calculation no longer seems to work. So i am guessing i should use something other than sameperiodlastyear.

any help is appreciated.

adding the dax i am using... if i put both in a matrix with the year_month the first distinct count works fine. but if i add the prev year i only get the total for the time period selected in the slicer. it won't give the sub totals at each of the months

Cur_year_count = CALCULATE(DISTINCTCOUNT(MAIN_FACT[ID_CUSTOMER_VISIT]), (MAIN_FACT[ID_CUSTOMER_VISIT] <>BLANK()) + 0)

Prev_year_count =  CALCULATE(DISTINCTCOUNT(MAIN_FACT[ID_CUSTOMER_VISIT]), (MAIN_FACT[ID_CUSTOMER_VISIT] <>BLANK()) + 0, SAMEPERIODLASTYEAR(MAIN_FACT[ORIG_SERVICE_DATE]))

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

For SAMEPERIODLASTYEAR Function is a Time-intelligence function. So you'd better use a dim date table for calculation as below:

Step1:

Create a dim date table, eg.

Date = CALENDARAUTO()

https://radacad.com/do-you-need-a-date-dimension

Step2:

Create the measure as below:

Prev_year_count =  CALCULATE(DISTINCTCOUNT(MAIN_FACT[ID_CUSTOMER_VISIT]), (MAIN_FACT[ID_CUSTOMER_VISIT] <>BLANK()) + 0, 
SAMEPERIODLASTYEAR('Date'[Date]))

Step3:

Use date field from dim date table instead of data table to create visuals.

here is my sample example:

and here is my sample pbix file, please try it.

Best Regards,

Lin