Time Intelligence - comparing this year to last year with a custom measure

[ledoyle1121]

Hi,

I have a report where I am trying to compare a team's Net Promoter Score % for this year and last year. The NPS value I am displaying is a custom measure that I've created which is: 

NPS = ('Direct Query - NPS'[NPS Promoter %]-[NPS Detractor %])

I've tried to calculate it a few different ways, the most recent being in this format: 

*NPS = CALCULATE([NPS])
*NPS Score Previous Year = CALCULATE([NPS],SAMEPERIODLASTYEAR(DATE_DIM[CALENDR_DATE]))
*Diff in NPS This Year and Last Year = [*NPS]-[*NPS Score Previous Year]

However, the visuals don't seem to be producing the expected results. Instead of seeing percentages between 40% and 100%, I'm only pulling back "1" in my visual. 

Anyone have any idea why this is happening? 

Thanks!

Lauren

PS: I've saved the file to dropbox, but am not super familiar with providing others access... hopefully this link works, otherwise I can share via email
 https://1drv.ms/u/s!AhHYOkEMipXmaKSEep9s224HmoQ 

[v-yuezhe-msft · Accepted solution]

@ledoyle1121,

Create a visual using date field of DATE_DIM table, you will get correct results for *Diff in NPS This Year and Last Year . You can review the modified PBIX file to get more details.



Regards,
Lydia

[Anonymous]

Hi Lydia,

Is it possible to see the pbix file, I'm trying to replicate this but i can't work out how you have done so.

David

[ledoyle1121]

Hi Lydia,

Thanks for this! I recreated the visual in my own file, using the sample you sent for reference. I'm wondering if you can help make sense of an error message I received when trying to drill down from the Year view to the Month:

"Couldn't load the data for this visual

MdxScript(Model) (67,71) Calculation error in measure 'Direct Query - NPS'[*NPS Score Previous Year]: Function 'SAMEPERIODLASTYEAR' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion"

In the sample file, the error message gives the numbers 67 and 71, but in my actual file where I tried reproducing it, the error message indicates 71 and 71. I'm not quite sure how to interpret the message, and any help is greatly appreciated!

[v-yuezhe-msft]

@ledoyle1121,

In your PBIX file, I have no issues when drilling down the chart using the highlighted button. Do you use CALENDR_DATE field of your DATE_DIM table as X-Axis and use the third button to drill down?



Regards,
Lydia

[ledoyle1121]

Hi Lydia, 

Not sure why it was giving me trouble yesterday, because everything works fine now and I haven't made any changes to the file. Must have been a fluke. Thank so much for your help!