Keeping track of the last status in tickets

[CountingPeople]

Hi all

I have a fairly simple data model, a table "Issues", the "StatusChanges" which tracks all changes in status with a timestamp for each ticket, and finally a calculated table "IssueStatusHistory". The last table is meant to track the latest status for each ticket and month. That should let me go back in time to see how many tickets have had state.

The column Status in IssueStatusHistory is what I want to use for that. It is important to note that the issues can switch status more than once during a month, so that is why the "statusChanges" table also contains the column "last status in month".

The problem now comes from the fact that an issue might be set to done in one month, but is then set to "To Do" again in a subsequent month. Also, some issues do not have any changes for more than one month (From Jan to April in the example below), and I want to make sure I populate those values for as long as the issue is in that status. As illustrated in the screenshots, the issue filtered in the table was changed to "Done" in May 2022, but then set to "To Do" in June. How can I change my Status Formula below to make sure I will always get the most recent change as per the StatusChanges table?

Thanks for the help!

IssueStatusHistory:

IssueStatusHistory
StatusChanges:

[CountingPeople · Accepted solution]

Finally got it to work, here's the final DAX:

Status = IF(
    [Created IDMonth]<[IDMonth],    
    CALCULATE(
      LASTNONBLANKVALUE(StatusChanges[CREATED],MAX(StatusChanges[Last Status in Month])),
      FILTER(StatusChanges,
         StatusChanges[ISSUE_KEY]=IssueStatusHistory[ISSUE_KEY]&&
         StatusChanges[IDMonth_changes]<=IssueStatusHistory[IDMonth])),
      "Not created yet")

The filtering based on IDMonth was still necessary, EARLIER() not really as each cell will run a table scan (as per my understanding). Finally, using LASTNONBLANKVALUE on Last Status in MOnth based on CREATED solved it. Thanks for the input @rubayatyasmin 

[rubayatyasmin]

Good to know that the reply helped. @CountingPeople congratulations on solving your problem. 

[rubayatyasmin]

Hi, @CountingPeople 

how about using LSTNONBLANK dax func to return the latest status change value?

just to give you an idea: Might need to adjust the column names. 

Latest Status =
CALCULATE(
LASTNONBLANK(StatusChanges[Status], StatusChanges[CREATED]),
FILTER(
StatusChanges,
StatusChanges[Issue_KEY] = EARLIER(Issues[Issue_KEY]) &&
StatusChanges[CREATED] = MAX(StatusChanges[CREATED])
)
)

refer:

https://learn.microsoft.com/en-us/dax/lastnonblank-function-dax

[CountingPeople]

Hi @rubayatyasmin Thanks for the input, that might actually work, but somehow in only get blanks in return. I tried both LASTNONBLANK() and LASTNONBLANKVALUE(). Might be that something is wrong with the filtering?

Here's my last attempt: 

Status = IF(
    [Created IDMonth]<[IDMonth],    
    CALCULATE(
      LASTNONBLANK(StatusChanges[Last Status in Month],StatusChanges[Last Status in Month]),
         FILTER(
            StatusChanges, 
            StatusChanges[ISSUE_KEY] = EARLIER(IssueStatusHistory[ISSUE_KEY])&&
            StatusChanges[CREATED]=MAX(StatusChanges[CREATED]))),
      "Not created yet")

Just to put some context I need to have the value in StatusChanges[Last Status in Month] put into the IssueStatusHistory[Status] column.