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.
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
Thanks! Will test this out.