Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Are you using a separate date table for time intelligence?
Proud to be a Super User!
No, but that can be easily done.
Try a measure
Value of Last Month = CALCULATE ( SUM ( 'Table'[Value] ), LASTDATE ( 'Table'[DATE] ) )
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
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |