Below two fields show exact same formula, but to differentiate the range, need to prepare two.
Remove duplicated items with filter (step2)
If you want to show relative last year with filter, you need to change all the filters to context to make LOd work.
If you want use absolute three latest years nevertheless of filtered item, you don't need to change to context.
3YrMedian_SM_10.2.twbx 3.4 MB
Thanks a lot for your response and explanation. Sorry I am a bit confused and probably didn't explain my self well.
- Process: Projects that have the completed the following events (PP and APPR)
- # of Projects: # of Projects that have completed the above Process. It is the count of Distinct projects.
- Median Duration: It is the Median # of days taken between those two events
- Difference from 3 Year Median: It is difference from above Median duration.(Median duration - 3 Yr Median)
To calculate 3 Year Median(take all the projects which have End date in 2014,2015 and 2016) and calculate the median.
My end output should look like the screenshot below. If I have projects in the window(detail card) how will I be able to summarize the numbers into single row.
The 3 Year Median should always be calculated for 2014,2015 and 2016 only.
But if the user filters for a specific country and for 2017 only the Median Duration should be for that Country and 2017 where as 3 year median should be for the 3 years(2014,2015 and 2016) for that country.
I hope I explained my problem a little better. Thanks so very much for your time
To show only the latest year, add one more index filter.
To calculated the LOD only in the range of filtered dimensions, change the Date filters to context.
3YrMedian_SM_10.2_v2.twbx 3.4 MB