Forum Discussion
Opportunities progress based on date slicer selection
Hi Everyone,
please your advice with the solution,
Little background,
I have a table of our sales opportunities that updates it self periodically from SQL. Let`s call it fact table.
For this example i will involve only the columns of Oppotunity,Probability,Amount and Query Date. While Query Date is the date of query by SQL from our CRM system and other Columns are self explanatory.
The goal and the requirement is to find a way to show progress of opportunities for the selected range of dates that specified by a slicer. i.e. If i choose the range of 12/2016 to 02/2017 i desire to look only the opportunities that match the last date of 12/2016 in order to see the progression for the next two months.
I think the answer is somehow to create another column/table that shows only the opportunities with dates that match the minimum point of selected date range for the last date of this minimum month that i could use in visual/page level filter.
I am including visual example for it:
Any help would be appreciated,
Andrey
Hi AndMich,
You can try to use below formula to add year month to filter the original table.
Calculate Column: Year Month(End) = IF(MONTH([Query Date]+1)<>MONTH([Query Date]),FORMAT([Query Date],"mm/yyyy" ),BLANK())
Regards,
Xiaoxin Sheng
3 Replies
Hi AndMich,
You can try to use below formula to add year month to filter the original table.
Calculate Column: Year Month(End) = IF(MONTH([Query Date]+1)<>MONTH([Query Date]),FORMAT([Query Date],"mm/yyyy" ),BLANK())
Regards,
Xiaoxin Sheng
Hi Xiaoxin, thank you for response,
Actualy my goal was to create some relationship between selected range of dates to the deals that was in the minimum point of this selection and see their progression overtime while the deals filter themselves according to the current selection.
In your example you created another slicer in order to choose the deals, is there another way to achieve the goal of automatic filtering the deals that corresponding the slicer selection ( one slicer only )?
I did it with two slicers of dates ( see example below). First slicer(month progress) to select the start date , and second(month/year) for the progression dates.
The goal of first slicer(month progression) is to filter only the opportunities that were exist for the selected query date,
the second slicer (Month/Year) defining the date range of presentation of the selected above opportunities.
I am attaching the data connection model:
the first slicer defined by DateForProgression table and the second by DateFilter table.
But I think that there is another way (i.e. only select one slicer) to achieve this goal.
Best Regards,
Andrey.
Hi AndMich,
>>In your example you created another slicer in order to choose the deals, is there another way to achieve the goal of automatic filtering the deals that corresponding the slicer selection ( one slicer only )?
Yes, only use date slicer also support your requirement.( Actual I add the type slicer to make my sample visual as same as the screenshot which you provided.)
Regards,
Xiaoxin Sheng
