Slicer for 30, 60, 90 days forward


#1

Continuation of my pipeline report. I am asked to give the ability to slice by time period (DAYS) for opportunities that are set to close in the next 30, 60, 90 days from today.

I have an [Estimated Closing Date] field to bump up against. Dates are set into the future. I have your Dates calendar Sam set for the whole 2018. I found a way to show past 30, 60, 90, but I am having issue making it work for days from today forward.

Thank you.


#2

Try using DATEDIFF function. The following example will give you the amount of days until your estimated closing date. Here is example of measure you could use:
AVERAGEX(Table Name,DATEDIFF(TODAY(),‘Table Name’[Estimated Closing Date],DAY))

You could use SUMX instead of AVERAGEX, but reason I use AVERAGEX is if I put the measure in a table in the totals at the bottom of the table will show the average, and if you use SUMX it will total all the #'s together. If your using this just as a slicer, doesn’t matter which one you use. If you use AVERAGEX, make sure you edit the decimal # to zero under the formatting tab at the top of the page so that you see 30 and not 30.00. Let me know how this work for you


#3

Thanks! Will test this out.