Hi Guys,
I have a requirement my client asked me show last 5 years sales in month and days wise and he wants to apply filter on month so that he can select which month he wants, based on the Days of the perticular month corresponding sales should show and he wants tool-tip should show Month- to-date sales means when he hover it on the sales it will give the running sum followed by days(eg: day1 10 sales, day2 5 sales+day1 10 sales ), some how i achieved this by using RUNNING_SUM(SUM(sales)) but i am not able to find out the solution for Year-to-date means if i hover on the 2015 june month sales it should sum up the all previous months(eg : i want june month means it should be sum of jan+feb+march+aprl+may and running sum of the current month)
and it should show the running sum of june month like that 2016,2017,2018,2019 i have to display how would i show it tool-tip . Could you please help me on this
it would be really greate if anyone can give a solution for this.
i have create a sample sheet for my requirement and i am attaching it.
Hi RAO,
It's been a while since you posted, so hopefully you found an answer by now!
Here's one way to get what you are looking for:
Step One: Create a Parameter to select Month of Order Date then choose 'Show Parameter Control'
Create a Parameter of Type Integer, as a List, with Values 1 thru 12, and Displayed as Months of the Year (as below) :
Once you're done, Right-Click on the Parameter and click 'Show Parameter Control'
Step Two: Create a Calculated field that you will use so that you can hide the rows that you need for the upcoming table columns you will use for your tooltips.
Date Show/Hide
iif(datepart('month',[Order Date])=[Order Date Parameter],1,0)
Step Three: Create a Calculated field for Month Number
Month Num
datepart('month',[Order Date])
Step Four: Add YEAR(Order Date) to Columns and the following into Rows: Date Show/Hide, Month Num, DAY(Order Date) so that your view should look like this:
Step Five: Hide the rows where Date Show/Hide are equal to zero
Right-Click on the column where you see zero displayed as above and click 'Hide'
Step Six: hide Headers for Date Show/Hide and Month Num
Right-Click on the Date Show/Hide pill in Columns and select 'Hide. Do the same for Month Num
Step Seven: Drag Sales to Text on the Marks card
Step Eight: Create a calculated field for Month to Date
Month to Date_JS
RUNNING_SUM(SUM(iif([Date Show/Hide]=1,[Sales],0)))
Step Nine: Drag Month to Date_JS to Tooltip on the Marks card
You then need to Right-Click on Month to Date_JS on the Marks card and choose Compute by->Table(down)
Step Ten: Create a calculated field for Year to Date
Year to Date
RUNNING_SUM(SUM(iif([Month Num]<=[Order Date Parameter],[Sales],0)))
Step Eleven: Drag Year to Date to Tooltip on the Marks card
You then need to Right-Click on Year to Date on the Marks card and choose Compute by->Table(down)
Step Twelve: Clean up your tooltip display
Click on Tooltip on the Marks card and format the Tooltip to look like you want
I've attached a workbook for reference.
Please let me know if this was helpful/answered your question!
Thanks,
John
