Use different calculations in line graph based on year in x-axis

[KrisD]

Hi!

I have written a somewhat lengthy measure which uses one of two values available in a slicer to produce values for a line graph.

I do however not want to perform this particular calculation on years other than the current. What I want is to have a different calculation take place if the year is not the current. This is where I've gotten a bit stuck. 

The main calculation:

Accident frequency =

VAR MultiplicationFactor = 10^6

VAR AverageLastYear = CALCULATE ( DIVIDE ( [Number of hours worked], 12 ),

ALL ( Period ),

Period[Offset Year] = -1 )

VAR SlicerValue = SELECTEDVALUE ( 'Slicer'[Value] )

SWITCH (

TRUE(),

SlicerValue = "Include current month",

DIVIDE ( SUM ( 'Fact Accidents'[Number off accidents with absence] ), ( [Worked hours] ), 0 ),

SlicerValue = "Exclude current month",

DIVIDE (

SUM ( 'Fact Accidents'[Number off accidents with absence] ), ( [Worked hours] - AverageLastYear ), 0 ) ) )

VAR Result = Frequency * MultiplicationFactor

RETURN

Result

Is it possible to simultaneously have this calculation take place ONLY for current year, and use another calulation for previous years?

[v-jingzhang · Accepted solution]

Hi @KrisD 

Yes, it is possible. Assume you have two measures, MeasureA for current year and MeasureB for previous years. You can create a measure like below and use it in the line chart. You can use different calculation in MeasueA and MeasureB according to your needs.

MeasureSample =
VAR currentYear = YEAR ( TODAY () )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Year] ) = currentYear, [MeasureA], [MeasureB] )

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[KrisD]

Great! I used CurrentYear to check what year it was in the visual, but failed to use SelectedValue which was the missing component to achieve my goal. Your input is much appreciated! 

The final result (if anyone in the future should ask a similar question in this forum):

VAR FrequencyExcl = IF (

SlicerValue = "Exclude current month" && SELECTEDVALUE ( Period[Year] ) = CurrentYear,

DIVIDE (

SUM ( 'Fact Accidents'[# of accidents with absence] ), ( [Worked Hours] ), 0 ),

DIVIDE (

SUM ( 'Fact Accidents'[# of accidents with absence] ), ( [Worked Hours] ) ) )

VAR FrequencyIncl = IF (

SlicerValue = "Include current month" && SELECTEDVALUE ( Period[Year] ) = CurrentYear,

DIVIDE (

SUM ( 'Fact Accidents'[# of accidents with absence] ), ( [Worked Hours] - AverageLastYear ), 0 ) )

VAR Result = IF (

FrequencyExcl, FrequencyExcl * MultiplicationFactor,

IF ( FrequencyIncl, FrequencyIncl * MultiplicationFactor ) )