YTD and MTD to selected filter date

[Anonymous]

Hello,

I have a report with a year, month and day filter (see picture below) and want to calculate the Net Promoter Score (NPS) for Year to Date (YTD) and Month to Date (MTD) up to the selcted day only.

For example, If I have chosen 2017, June, 27 I want the YTD to calculate from 1 Jan-27 Jun 2017, and MTD from 1-27 jun 2017.

My NPS formula is as follows, which at the moment calculates the NPS for the specific dates selected in the filters.

NPS = ((CALCULATE(COUNT('GSS'[AC04]),'GSS'[AC04]>8)-CALCULATE(COUNT('GSS'[AC04]),'GSS'[AC04]<7))/CALCULATE(COUNT('GSS'[AC04]),'GSS'[AC04]>=0)*100)

Any help much appreciated.

Many thanks,

George

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

According to your description above, the formulas below should work in your scenario. 

NPS YTD = TOTALYTD ( [NPS], 'Date'[Date] )

NPS MTD = TOTALMTD ( [NPS], 'Date'[Date] )

Note: just replace 'Date'[Date] with the date column name in your Calendar table.

Regards