Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dfralmeida
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
Community Champion
Community Champion

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!




No, but that can be easily done. 

@Dfralmeida

Try a measure

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

Capture2.PNG

 

Capture.PNG

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.