cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dfralmeida Frequent Visitor
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
KHorseman Super Contributor
Super Contributor

Re: Show SUM of MAXDate for each year

Are you using a separate date table for time intelligence?


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
Dfralmeida Frequent Visitor
Frequent Visitor

Re: Show SUM of MAXDate for each year

No, but that can be easily done. 

CheenuSing Super Contributor
Super Contributor

Re: Show SUM of MAXDate for each year

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

 

 

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

Proud to be a Datanaut!
Highlighted
Moderator Eric_Zhang
Moderator

Re: Show SUM of MAXDate for each year

@Dfralmeida

Try a measure

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

Capture2.PNG

 

Capture.PNG

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 107 members 1,586 guests
Please welcome our newest community members: