Line Chart (Revenue over Years)

hi paulthangaraj ,

Create a column in your data as follows:

Year Group = IF([Project Start Year] = YEAR(TODAY()), "Current Year",
                           IF([Project Start Year] < YEAR(TODAY()), "Previous Year", "Next Year"))

Create a line chart.

Move Year Group  to the axis area, Region column to legend area and move your sumaarised value or a calculation to Values area.

Thanks,

Pragati

paulthangaraj - You will want to create a disconnected table using an Enter Data query that contains the values in your axis. Then you create a measure that does a MAX on this disconnected table to grab the value in the axis. You can then use this in a SWITCH statement to concoct the desired calculation for that axis point. So, something like:

Measure =
  VAR __AxisValue = MAX('Table'[Value]) // this is your Enter Data disconnected table
RETURN
  SWITCH(__AxisValue,
    "Current Year",SUMX(FILTER('DataTable',[Project Start Year] = YEAR(TODAY())),[Revenue Current Year]),
    "Next Year",SUMX(FILTER('DataTable',[Project Start Year] = YEAR(TODAY())),[Next Year]),
    "in Two years",SUMX(FILTER('DataTable',[Project Start Year] = YEAR(TODAY())),[in Two Years])
  )

That's one way. The other way would be to unpivot your last three columns in Power Query. That would probably be preferred. Then you don't need the measure or disconnected table and it is dirt simple.