VAR/Return Measure over time

[LRiley]

Hi, 

I'm hoping someone can help with this one, I have calculated the variance between the total and each row to show the impact on the overall calculation as below

 This has worked perfectly when you select one date/period on the column 

when I select more than on date period in the column the calculations are no longer correct 

Any help would be appreciated 

Thanks 

Liam

[mahoneypat]

I would first try adding the week filter back into the calculation with VALUES('Date'[WeekColumn]) into each of your CALCULATE() expressions.

..., ALLSELECTED('NPS_Deep_Dive'), VALUES('Date'[WeekColumn]))  // replace with your actual week column name

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[LRiley]

Hi Pat,

Thanks for the quick reply and the suggestion, I have tried several variations of adding a date to the filter, as I thought All Selected would remove the filter across the date columns also. however, I only get the NPS score for the NPS_Impact calculation

NPS_Impact = 

VAR Prom =
      [NPS_PRO] - CALCULATE ( [NPS_PRO], ALLSELECTED ( 'NPS_DEEP_DIVE' ), VALUES('NPS_DEEP_DIVE'[WeekStart_Case]) ) 
VAR Det =
      [NPS_DET] - CALCULATE ( [NPS_DET], ALLSELECTED ( 'NPS_DEEP_DIVE' ) , VALUES('NPS_DEEP_DIVE'[WeekStart_Case])  )  
VAR Survey =
     [NPS Surveys] - CALCULATE ( [NPS Surveys], ALLSELECTED ( 'NPS_DEEP_DIVE') , VALUES('NPS_DEEP_DIVE'[WeekStart_Case])  )
VAR NPS =
    CALCULATE([NPS %],ALLSELECTED(NPS_DEEP_DIVE),VALUES('NPS_DEEP_DIVE'[WeekStart_Case]) )
RETURN
    IF ( [NPS %],NPS- DIVIDE ( Prom - Det, Survey ))

I'm sure it will be something very simple and easy to correct, I have just looked at it for too long. 

[LRiley · Accepted solution]

Hi, I have solved the issue using all except, as below 

NPS_Impact = 

VAR Prom =
      [NPS_PRO] - CALCULATE ( [NPS_PRO],  ALLEXCEPT( 'NPS_DEEP_DIVE',NPS_DEEP_DIVE[WeekStart_Case]))
VAR Det =
      [NPS_DET] - CALCULATE ( [NPS_DET],  ALLEXCEPT( 'NPS_DEEP_DIVE',NPS_DEEP_DIVE[WeekStart_Case])) 
VAR Survey =
     [NPS Surveys] - CALCULATE ( [NPS Surveys], ALLEXCEPT( 'NPS_DEEP_DIVE',NPS_DEEP_DIVE[WeekStart_Case]))
VAR NPS =
    CALCULATE([NPS %],ALLEXCEPT( 'NPS_DEEP_DIVE',NPS_DEEP_DIVE[WeekStart_Case]))
RETURN
    IF ( [NPS %],NPS- DIVIDE ( Prom - Det, Survey ))    

Thanks again