Good morning .. I will try and describe my challenge as best as possible ..hope it will make sense.
1. I have two dates from which I derive a DATEDIFF per category called the Lead Time
2. There are many rows in the dataset each with its own Lead Time over a particular period of time
3. These Lead Times are based on various categories such as Port of Departure and Date of Departure
4. I have also created a DATE TABLE linking the DATE in the Date Table to the requried date in the actual data
5. So as an example: From the port of Shanghai the Lead Time is 4 days for Nov 2019 (and various other Lead Times for lots of other dates in 2019) but I would use a date filter and choose a month in 2019) .. so would then choose Nov 2019 showing the 4 days as stated (or a combination of dates in 2019 showing the median of the then calculated Lead Time).
6. I would then like to see the variance (days and %) of the Lead Times, based on that Port of Departure in each month of 2020. So if the Lead Time in Jan 2020 from Shanghai is 10 days and the Lead Time from Shanghai in the chosen range in 2019, as in point 5, was 4 then the variance would be 6 and the % would be a positive 150%
7. I would like to see Point 6 for each of the months in 2020 as the year progresses .. so while the month of February 2020 progresses I would like to see that % change as the data is refreshed daily
8. I would then like t show this on a map where the circle would be green if it was a negative % and red if it was a positive %.
I hope I have explained this clearly.
thanks
Marc
