Show SUM of MAXDate for each year

[Dfralmeida]

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.

[KHorseman]

Are you using a separate date table for time intelligence?

[Dfralmeida]

No, but that can be easily done. 

[Eric_Zhang]

@Dfralmeida

Try a measure

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

[CheenuSing]

Hi @Dfralmeida

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