cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
Super User I

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
Microsoft
Microsoft

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
Ignite

Microsoft Ignite

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

Get Ready for Power BI Dev Camp

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