Summing two years as a single year

[Anonymous]

Hi, I have a pretty complex issue that I am working on and I am wondering if PowerBI has the capability to do this.

I am calculating energy savings from energy conservation projects, where I need to sum the one column from this year, and another column from last year. For example, the total 2020 savings would be (column A total in 2019) plus (column B total in 2020).

Every attempt I have made has resulted in (A in 2019 + B in 2019) and (A in 2020 + B in 2020) when I try to add the year field to my chart.

I have a large calendar table which has year offsets etc but I can't figure out a way to make use of that for this task.

If anybody has some ideas I could try that would be amazing.

thanks

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could create a measure such as:

Measure = CALCULATE(SUM([A]),FILTER('Table',YEAR([Date])=2019))+CALCULATE(SUM([B]),FILTER('Table',YEAR([Date])=2020))

The final output is shown below:

If the problem is still not resolved, please provide  the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , sum of two columns in not a problem 

You can select a five year range on page or can control year in measure 

Assume date in the table is joined with data of date and no date filter on page 

measure =

var _max = year(maxx(allselected('Date'), 'Date'[Date]))

var _min =_max -5

return

calculate(sum(Table[A]) + sum(Table[B]), filter('date', 'date'[Year] >=_min && 'Date'[Year] <=_max) )

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.