cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors