Surely this is possible?... Relative Dates across multiple measures

[RichieDazza123]

Hey DAX fans: struggling with this.

I have a series of measures which calculate the satisfaction score for differet car manufacturers (dummy data). Each individual is askied to rank each manufacturer as they've driven all of these cars (factTable is ''surveycompleted-ID' and contains multiple car ratings for each submission)

However I want to know satisfaction score (NPS) for each manufacturer across Last30Days, Last90Days, PreviousYearLast30Days etc. The trouble is, NPS isn't a calcuated column, it's a measure because it's based on an aggregation:

**If you're not familiar with NPS (satifation score), I've posted the calculation below, in short it's: (((no. of Promoters - no. of Detractors)/Number of Respondents)*100) and returns a decimal based score.  A promoter is someone who submits a satisfaction score of 9 or 10 and detractor is anyone who submitted a score of 0-6. Those who scored 7/8 are cateogrised as passive.**

** The Dax below works perfectly and is applied to each manufacturer

Kia_NPS

This is fine, however I'm having serious trouble when trying to compare L30D performance of these measures side-by-side. The below table is exactly what I want to achieve: 

If I try this by year it works, because the dates are based on column data:

But how do I go about creating a relative time measure that applies across all of these measures? The time measure needs to sit across multiple measures at once - anyone got any ideas of how to achieve this? I've tried SUMMARIZE but it only wants column name's, which I don't have.

Thanks,

Dale

[Anonymous · Accepted solution]

hmm i think you could try :

last 30 days = calculate(measure,datesbetween(dimdate[date],max(dimdate[date])-30,max(dimdate[date])))

last 90 days = 
calculate(measure,datesbetween(dimdate[date],max(dimdate[date])-90,max(dimdate[date])))

now for the previous 30 days you can do something like 

last period of 30 days measure = CALCULATE([TOTAL],DATESBETWEEN(Table1[Date],MAX(Table1[Date])-60,MAX(Table1[Date])-30))

 and finally 

same period last year for 30 days = CALCULATE([last 30 days],SAMEPERIODLASTYEAR(Table1[Date]))