cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Show SUM of MAXDate for each year

Hello,

I'm trying to create a chart that shows a calculated information but I'm not getting what I want, so I need some tips.

I have a table with information consolidated at the month level. So, when I'm exibiting the information at the higher level (Year) it consolidates the information (SUM), but I need for that only the last month of the year. How can I do it? I'm trying to implement it on a bar chart.

Example:

Values for 2015

Jan: 2

Feb: 4

Mar: 10

Values for 2016

Jan: 2

Feb: 2

Mar: 2

Apr: 3

May: 4

Jun: 5

Jul: 5

Aug: 6

Sep: 7

Oct: 8

Nov: 8

Dec: 9

Year (Higher Level):

2016: 61

2017: 16

--------------

What I need to show when in year level (Last value for the last month of that year):

2016: 9

2017: 14

Thanks for the help.

4 REPLIES 4
Highlighted
Super User I

## Re: Show SUM of MAXDate for each year

Are you using a separate date table for time intelligence?

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Highlighted
Frequent Visitor

## Re: Show SUM of MAXDate for each year

No, but that can be easily done.

Highlighted
Super User I

## Re: Show SUM of MAXDate for each year

I did the following

1. Create a column called MonthNumber

MonthNumber = SWITCH ([Month],"Jan",1,"Feb",2,"Mar",3,"Apr",4,"May",5,
"Jun",6,"Jul",7,"Aug",8,"Sep",9,"Oct",10,"Nov",11,"Dec",12)

2. Create a column called YearMonth

YearMonth = [Year]*100+[MonthNumber]

3. Create a column called MaxMonthCol

MaxMonthCol = CALCULATE(MAX([MonthNumber]),FILTER(SalesbyMonth,[Year]=EARLIER(SalesbyMonth[Year])))

( where SalesByMonth is your table )

4. Create a column called IsMaxMonth

IsMaxMonth = If ([YearMonth]=[Year]*100+[MaxMonthCol],1,0)

5. Now create a Measure Sales for the max month in a year

SalesMax = Calculate (Sum([Value]), SalesbyMonth[IsMaxMonth] = 1)

6. Plot Year, SalesMax in a table report and you should get what you want.

If this solves your issue, please accept this as a solution and also give KUDOS.

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Highlighted
Microsoft

## Re: Show SUM of MAXDate for each year

@Dfralmeida

Try a measure

```Value of Last Month =
CALCULATE ( SUM ( 'Table'[Value] ), LASTDATE ( 'Table'[DATE] ) )
```

Announcements

#### Microsoft Ignite

This will be a conference that you do not want to miss!

#### Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors