cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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)

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

1 ACCEPTED SOLUTION
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])))``````

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

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

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

Announcements

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors