cancel
Showing results for 
Search instead for 
Did you mean: 
amitchandak

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

Polls
What is your favorite Power BI Feature release this month?