Display label only at the end of the MTD line and display monthly target on all dates.
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
Step : 1. Load data to Power BI
2. Add Date table with Date KeyDate 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.
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])
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.
File at https://community.powerbi.com/t5/Data-Stories-Gallery/Retail-Analytics-Analyzing-Retail-Sales-and-Ta...
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
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.
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!
check this part
If you have some sorting, Min and Max will do that. put the measure you created on the line.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.