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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kathyyy19
Helper III
Helper III

How to only show the latest numbers on a line chart?

Hello everyone!

 

I created a line chart in power bi desktop but I only want to show the latest numbers. Can anyone show me how to do that please? 

 

Original chart has all numbers showing for each month 

kathyyy19_0-1688154818602.png

 

The result I'm hoping to achieve (below is from excel that I was able to delete the numbers from previous month mannually):

kathyyy19_1-1688154950626.png

 

Thank you in advance for your help!

 

4 ACCEPTED SOLUTIONS

lucadelicio_0-1688396947664.png

 

lucadelicio_1-1688396972781.png


Create the two measure above.

I hope I have been helpful.
Mark it as a solution if resolve your problem.

Ciao!

Luca D'Elicio

https://www.linkedin.com/in/luca-d-elicio-74a481158/

View solution in original post

lucadelicio_0-1688397892235.png

 

Foglio1 is the table [data] is the field of the calendar date.
in italian DATA is DATE.

Luca D'Elicio

https://www.linkedin.com/in/luca-d-elicio-74a481158/

View solution in original post

Replace the refer of the date to the new field of the new table like this:

Max Data =
CALCULATE(
    MAX(Table1[Startofmonth])
    ,ALL(Table1)
)
--------
Color =
IF(MAX(Table1[Startofmonth]) = [Max Data]"#000000""#FFFFFF00")


I hope I have been helpful.
Mark it as a solution if resolve your problem.

Ciao!



Luca D'Elicio

https://www.linkedin.com/in/luca-d-elicio-74a481158/

View solution in original post

Yes in ALL and ALLSELECTED formula there is the solution if you have a date table with futures dates.

I'm happy I have been helpful.
Mark it as a solution if resolve your problem.

Ciao!

Luca D'Elicio

https://www.linkedin.com/in/luca-d-elicio-74a481158/

View solution in original post

19 REPLIES 19
lucadelicio
Super User
Super User

My fault, I missing double quotes and i use white instead of transparent.
Here is correct with two measure:

-----
Max Data =
CALCULATE(
    MAX(Foglio1[Data])
    ,ALL(Foglio1)
)
--------
Color =
IF(MAX(Foglio1[Data]) = [Max Data], "#000000", "#FFFFFF00")
Then pass Color to conditional formatting.
Here the pbix link
https://we.tl/t-pJTAEJYuTv


I hope I have been helpful.
Mark it as a solution if resolve your problem.

Ciao!

Luca D'Elicio

https://www.linkedin.com/in/luca-d-elicio-74a481158/

Hi Lucadelicio,

 

Thanks for your fast reply. Do you mind sharing with me a screenshot of your pbix? my company blocked the site you sent me.

lucadelicio_0-1688396947664.png

 

lucadelicio_1-1688396972781.png


Create the two measure above.

I hope I have been helpful.
Mark it as a solution if resolve your problem.

Ciao!

Luca D'Elicio

https://www.linkedin.com/in/luca-d-elicio-74a481158/

Hi Lucadelico,

Sorry to bother you again.. do you mind expanding the data table so I can see what column you are referring to in your dax please? I got a bit confused by foglio1[data]

lucadelicio_0-1688397892235.png

 

Foglio1 is the table [data] is the field of the calendar date.
in italian DATA is DATE.

Luca D'Elicio

https://www.linkedin.com/in/luca-d-elicio-74a481158/

send me your mail in private message please

Luca D'Elicio

https://www.linkedin.com/in/luca-d-elicio-74a481158/
lucadelicio
Super User
Super User

Try to use the conditional formatting passing a measure that returns the color with the condition in wich date it will be.
MEASURE =
var _maxdate =
CALCULATE(
MAX(Sales[date])
,ALL(Sales)
)
RETURN
IF(_maxdate < MAX(Sales[date]), #FFFFFF, #000000)
I use white and black.
White i think is the color of your background.

I hope I have been helpful.
It's more easier to help you if you upload your pbix.
Mark it as a solution if resolve your problem.

Ciao!

Luca D'Elicio

https://www.linkedin.com/in/luca-d-elicio-74a481158/

Hi Lucadelicio,

 

I tried to reply to your PM but the system said I reached the maximum number of pm so I couldn't send it.

 

I have a follow up question. 

I've got two tables, one transaction table and one calendar table. See below.

I created relationship by linking 'calendar date' with 'Date'. I want to use 'start of month' in X-axis in the line chart. In this case, how should I modify the dax you created?

 

Table 1 is the calendar table (I didn't write out all dates)

Calendar datestart of month
5/1/20225/1/2022
5/2/20225/1/2022
5/3/20225/1/2022
6/1/20226/1/2022
6/2/20226/1/2022

 

Table 2 shows transactions

DateABCDE
5/1/20223721 21
5/2/2022$5$9$10$45$50
5/3/2022$15$12$18$34$35
6/1/2022$20$16$22$28$34
6/2/2022$12$30$25$45$60

Replace the refer of the date to the new field of the new table like this:

Max Data =
CALCULATE(
    MAX(Table1[Startofmonth])
    ,ALL(Table1)
)
--------
Color =
IF(MAX(Table1[Startofmonth]) = [Max Data]"#000000""#FFFFFF00")


I hope I have been helpful.
Mark it as a solution if resolve your problem.

Ciao!



Luca D'Elicio

https://www.linkedin.com/in/luca-d-elicio-74a481158/

Hi Lucadelicio,
Sorry for the delayed reply. I took some time to try the Dax you sent me. I twisted it a little bit and it worked!!! Instead of using ALL, I used ALLSELECTED. I also watched a youtube video from Curbal (Title of the video: DAX Fridays! #184:Show last label only on a line chart in Power BI) and got some inspiration from there in combination of your dax 🙂
 
Thank you very much for taking the time to help me! I had been stucked with this issue for over 3 weeks and finally solved it! :') I really appreciate your patience in explaining all this to me and your help on this means a lot to me!!! Graize mille!!

Yes in ALL and ALLSELECTED formula there is the solution if you have a date table with futures dates.

I'm happy I have been helpful.
Mark it as a solution if resolve your problem.

Ciao!

Luca D'Elicio

https://www.linkedin.com/in/luca-d-elicio-74a481158/

If I use  MAX(Table1[Startofmonth]), it will be december 1st because my calendar table goes up to december. However, my transaction table only has data from most current month. 

Send me in email the fake pbix that rappresent your situation and I will fix tou

Luca D'Elicio

https://www.linkedin.com/in/luca-d-elicio-74a481158/

Hi Lucadelicio, Thanks for your help. I tried your dax in power bi but it didn't work. There are red lines on and after the word 'Return'. Do you know where I did wrong? (I copied your dax and replaced the fields with the field in my tables)

can you share the pbix. It's easier to help you.

Luca D'Elicio

https://www.linkedin.com/in/luca-d-elicio-74a481158/

I'm sorry I can't share it due to company privacy policy

share me a excel seample fake data.
So i can try on your dataset.

Luca D'Elicio

https://www.linkedin.com/in/luca-d-elicio-74a481158/

The table shows the data used for creating the first chart in my original post

 ABCDETotal
5/1/20223721 2152
6/1/2022$5$9$10$45$50$119
7/1/2022$15$12$18$34$35$114
8/1/2022$20$16$22$28$34$120
9/1/2022$12$30$25$45$60$172

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors