Column chart showing multiple years from single SharePoint list

[Anonymous]

i want a column chart that shows completed projects divided between client work and internal work annually. I have a SharePoint list that has a column with the date of completion of projects and another column with the client/internal work split. It is easy enough to show the completed work for one year by filtering the dates but how do I do this for multiple years.

The example below is what I am looking for - this uses an excel file specifically set up to show the data, but I do not want to continue to use excel for the data, instead linking it to the SharePoint list. 

Cheers

Steve

[v-zhenbw-msft]

Hi @Anonymous ,

Sorry for that we are not clear about your issue.

Do you want to add up the data every two years?

If yes, you can refer the following steps.

1. Create three columns, year column, next year column and combine column.

Year = YEAR('Table'[Date])

Next_year = 
var _lastYear = 'Table'[Year]+1
return
IF(
    'Table'[Year]=YEAR(TODAY()),'Table'[Year],_lastYear)

Yea_nextYear = 
var _x='Table'[Year]&"/"&'Table'[Next_year]
return
IF(
    'Table'[Year]='Table'[Next_year],CONVERT('Table'[Year],STRING),_x)

2. Then we can create a measure to get the result.

Measure = 
CALCULATE(
    SUM('Table'[value]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Year]<=MAX('Table'[Year])+1&&'Table'[Year]>=MAX('Table'[Year])))

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[Anonymous · Accepted solution]

Thank you for your suggested solution. I have managed to figure out how to achieve my desired result, without resort to additional formulas (see below).

[amitchandak]

@Anonymous , need to look at data.

This is a stacked visual. With close date(or date from date table joined with close date) on-axis.  Client type as a legend. Count of the project or some other measure as values.

Stacked visual -https://www.youtube.com/watch?v=fYoQJFBldlY