Creating %age Change from Best Ever in a week to Current Week

DAX DAX Calculations
1

I have 2 years worth of data . I want to do an efficiency index with following formulae :

Highest impressions in Any Week in 2 years / Impressions this( selected ) week .

The Approach I thought could work was that I will need to calculate Impressions PER week for all Dates using Calculated Measure .

I have tried with

_SUMWeeklyImpressions = CALCULATE(

SUM(ImpressionShare[Impressions]),

ALLEXCEPT(‘Date’,‘Date’[FW Week Year])

)

However, ALL Except is not working .

Date table is linked with the other table .

I have attached file for reference here .

Data-Problem.pbix (1.1 MB)

2

The question is :

  1. Why is ALLEXCEPT Not working when both tables are linked
  2. Is there a better approach of solving this problem ?

Thanks

3

Check out how I have worked through this here for ideas on how to solve this

Also if you are working with weeks specifically, also see how I’ve done it here.

This is the exact answer I don’t believe but I’m confident that what’s in these tutorials is ultimately what you need to solve this.

Sam