Reply
kathyyy19
Helper III
Helper III
Partially syndicated - Outbound

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

Syndicated - Outbound

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

LinkedIn Profile

View solution in original post

Syndicated - Outbound

lucadelicio_0-1688397892235.png

 

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



Luca D'Elicio

LinkedIn Profile

View solution in original post

Syndicated - Outbound
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

LinkedIn Profile

View solution in original post

Syndicated - Outbound

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

LinkedIn Profile

View solution in original post

19 REPLIES 19
lucadelicio
Super User
Super User

Syndicated - Outbound

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

LinkedIn Profile

Syndicated - Outbound

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.

Syndicated - Outbound

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

LinkedIn Profile

Syndicated - Outbound

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]

Syndicated - Outbound

lucadelicio_0-1688397892235.png

 

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



Luca D'Elicio

LinkedIn Profile

Syndicated - Outbound

send me your mail in private message please



Luca D'Elicio

LinkedIn Profile
lucadelicio
Super User
Super User

Syndicated - Outbound

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

LinkedIn Profile

Syndicated - Outbound

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

Syndicated - Outbound
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

LinkedIn Profile

Syndicated - Outbound
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!!

Syndicated - Outbound

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

LinkedIn Profile

Syndicated - Outbound

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. 

Syndicated - Outbound

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



Luca D'Elicio

LinkedIn Profile

Syndicated - Outbound 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)

Syndicated - Outbound

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



Luca D'Elicio

LinkedIn Profile

Syndicated - Outbound

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

Syndicated - Outbound

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



Luca D'Elicio

LinkedIn Profile

Syndicated - Outbound

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

Syndicated - Outbound
 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
avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)