I have a dataset pulled from an excel sheet that is manually updated. There is a Task column with 3 values and sometimes a task will go months without any updated values. I'm setting up a report for monthly numbers that will show the latest added values for each task in a month, and if the month had nothing added, uses the most recent values added from a previous month. I also added an index column as sometimes there will be multiple updates on the same day but no timestamp. I was able to pull this off with the following measure:
VAR SelectedMonth = MAX('Date'[Date])
VAR SelectedTask = "instance segmentation"
RETURN
CALCULATE(
LASTNONBLANK('Sheet1'[AP50], 1),
FILTER(
ALL('Sheet1'),
'Sheet1'[Task] = SelectedTask &&
'Sheet1'[Train Date] <= SelectedMonth &&
NOT ISBLANK('Sheet1'[AP50]) &&
RANKX(
FILTER(
ALL('Sheet1'),
'Sheet1'[Task] = SelectedTask &&
'Sheet1'[Train Date] <= SelectedMonth &&
NOT ISBLANK('Sheet1'[AP50])
),
'Sheet1'[Index],
,
DESC,
Dense
) = 1
)
)
I've copied a version of this measure for different tasks and values, and have all worked great. I tested it with a date slicer and everything is showing correct values.
However, one of the tasks is broken down into mutiple labels in a [Label Name] column, and when I adjust the SelectedTask variable to the updated task and add it to a table with the these labels, they all show the same value. It is also blank for months where nothing was updated for the task. I'm thinking it's just a filtering issue but I'm not sure what needs adjusting.
