cancel
Showing results for 
Search instead for 
Did you mean: 

Display Label Only on the Last Data Point of the Line Visual for a Trend

Problem Description:

Display label only at the end of the MTD line and display monthly target on all dates.

 

Data:

Sales Data – Daily. Need data label at the end of MTD line

Target Data – Only once per month without Date. Need to display the same amount on all dates

Screenshot 2020-07-24 13.42.45.pngScreenshot 2020-07-27 13.34.28.png

Step :
1. Load data to Power BI

Screenshot 2020-07-27 13.57.37.pngScreenshot 2020-07-27 13.57.55.png

 

2. Add Date table with Date Key
Date Table: https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

 

 

 

Date = CALENDAR(date(2013,01,01),date(2018,12,31))
DateKey = FORMAT([date],"YYYYMMDD")

 

 

 

 

3. Add end Date to Target Table

 

 

 

Date = "01-" &[Order_Month] & "-" &[Years]
End Date = EOMONTH([Date],0)
DateKey = FORMAT([End Date],"YYYYMMDD")

 

 

 

4. Add date key to the sales table also.

 

 

DateKey = FORMAT([order Date],"YYYYMMDD")

 

 


6. Join both Sales and Target to the date calendar on the Date Key.

Screenshot 2020-07-27 13.58.39.pngScreenshot 2020-07-27 13.58.57.png


7. Create the following measures:

 

 

Sales(Net) = sum(Sales[Sales])

Target = SUM(Target[Total])

EOM Target = CLOSINGBALANCEMONTH([Target],'Date'[Date])

MTD Last Day Sales = var _max =maxx(ALLSELECTED('Date'),'Date'[Date])
return
CALCULATE([MTD Sales],FILTER('Date','Date'[Date]=_max))

 

 

 


8. Plot the line visual under the data label section used customize series option. Hide the label for Sales(Net) and EOM Target. It makes only 2 labels visible that we need at the end of the line.

Screenshot 2020-07-27 14.20.12.png


File at
https://community.powerbi.com/t5/Data-Stories-Gallery/Retail-Analytics-Analyzing-Retail-Sales-and-Ta...

 

Screenshot 2020-07-27 14.02.05.png

 

 

Let us know what you think about the above suggested method. If there is any other topic in your mind, do not hesitate to reach out to me.

 

My last blog series

Creating Financial Calendar - Decoding Date and Calendar 1-5 - Power BI Turning 5 Celebration Series

Any Weekday Week - Decoding Date and Calendar 2-5 - Power BI Turning 5 Celebration Series

Week That Resets Yearly - Decoding Date and Calendar 3-5 - Power BI Turning 5 Celebration Series

Traveling Across Workdays - Decoding Date and Calendar 4-5 - Power BI Turning 5 Celebration Series

Date as Dateadd - Decoding Date and Calendar 5-5 - Power BI Turning 5 Celebration Series

Comments

Hi @amitchandak , 

 

I was actually thinking of something like this recently so thanks for a great article.

 

My case is that I have a team where I need to compare values against other team ratios. I am using a Line & Stacked Bar visual and I would like to only show the data label once at the end of the line to the right.

Line & Stacked Column Visual.png

 

As your case is dates which makes sense, how could I use the technique for non-date based visuals?

 

Thanks a million and nice work!

David

@Chthonian ,

check this part

CALCULATE([MTD Sales],FILTER('Date','Date'[Date]=_max))

 

If you have some sorting, Min and Max will do that. put the measure you created on the line.