Hello everyone I did my best to explain the issue that I have on the topic. For a dashboard I have built I have a measure that is supposed to show the most recent value when no date filters are applied.
I am currently replicating this DHBD for another country and have run into a problem when there is a location that no longer exists and it is showing in the bar graph with its most recent value.
I have attached some mock files to give you an idea of what I am working with. What I use for this report is a list of employee for each month to get the historic of Headcount and FTE at the end of each month.
The problem is with the most recent measure if I use it alone it gives me the correct value, in the case of these test files 2. As soon as I add location it includes the location that does not exist at the max date. In my head, once I add the location context the formula is calculating the max date for each location and returning that value, but that is not what I want. I want it to show the data as of the most recent date in the table when there is no filter and then when I filter to show the data for that specific date.
I know this might not be clear so please just ask me if you need more clarifications.
Hi @gaius! Welcome to the Forum! It’s great to know that you are having a great experience so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
Hi @Yatz86, thank you for your time in trying to solve the problem. Unfortunately, it is still giving me the same results I achieved. If I use this measure alone it should show me the data filtered as of the most recent date, meaning that location 3 should not be showing in the graph by location. Notice how location 3 is not on the report after the month of may. See below two screenshots, the first one is what the measure alone, with not filters applied, should be showing, the second one is what it shows.
I also tried a solution with the last date function that actually worked in not including that third location, but I ran into another issue. In the dashboard I show a time series of the fte to see the evolution over time, when I was not filtering the data it only showed one column for the latest date instead of all the months in the specified period.
I am not too good at explaining things in English sometimes so please let me know if more context is required and I will gladly elaborate more.
Thank you for your help! This solution has worked just want to point out that I had to add a global filter on the Dashboard to limit my date table to the max date I have in my data sources.
Since I have just information up until the 28/02/2021 and the date table goes all the way to the 31/12/2021 the graph comes blank because I do not have info on that date, but there is no major issue here, I just update this graph on a monthly basis, so doing this small piece of manual work will not kill me.
