Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
RichieDazza123
New Member

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

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)

 

RichieDazza123_0-1667066529387.png


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_NPSKia_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: 

RichieDazza123_2-1667066879006.png

 

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

 

1 ACCEPTED SOLUTION
eliasayy
Impactful Individual
Impactful Individual

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

 

 

Screenshot 2022-10-29 220135.png

 

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

 

View solution in original post

1 REPLY 1
eliasayy
Impactful Individual
Impactful Individual

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

 

 

Screenshot 2022-10-29 220135.png

 

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

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors