I have a tool that provides me with informations about my vehicle fleet. It tells me whether a vehicle is "under management" or not (this is a variable that takes values 0 or 1), the type of energy the vehicle uses ("green energy" or not, a variable that takes values 0 or 1),the "stock entry date" (from 2018 up to the current year 2023) and a Parc Id to identify the vehicle. Every day, we take a photo of the fleet and store it in our data history. For this purpose, we have a variable called "photo date." We started this process on 01/04/2022, which is the oldest date we have. Today, we are creating a graph that shows the number of alternative energy vehicles added to the fleet each year.
The problem is that we perform this calculation using the latest available date (i.e., we take the most recent photo and count based on the "stock entry date" variable). Therefore, if there were vehicles that entered in 2021 and exited in 2023, they wouldn't be included in our graph using the latest date. The idea is as follows:
Since the oldest photo date is unfortunately only 01/04/2022, we want to use the figures for the years 2018, 2019, 2020, and 2021 based on the figures from 01/04/2022, regardless of the photo date, even if we filter based on today's date.
For the subsequent years, depending on the filter date, if the year has ended, display the figures from 12/31, and if not, display the figures from the photo date (for example, if we filter for 08/07/2023, we count vehicles from 2023 up to 08/07/2023 and data from 2022 up to 12/31/2022).
I hope my explanation is clear. Please don't hesitate to ask if you need further clarification. Thank you very much for your help!
