DAX help - Summarize based on the date and latest status

[tanquoc0309]

I have the data:

And I want to show the report:

 

I need to know the last status (New String) of each issue until the date.

Ex: Issue A, changed status many times (30/3 changed to In Progress, 2/4 changed to Resolve, 4/4 change to Reopened, 10/4 changed to Closed.

So: 

The chart will show:
30/3 -> +1 ticket (A) for status In Progress
31/1 -> 1/4 -> +1 ticket (A) for status In Progress (Because A didn't change during that time) 

2/4 -> + 1 for Resolve, (No + for In Progress)...

I try 2 formulas but the performance is very slow. My idea is calculate for each status:

L_S_In Progress = 
VAR MaxDateView =
    CALCULATE ( LASTDATE ( DimDate[Date] ) )
RETURN
    CALCULATE (
        COUNTROWS (
            FILTER (
                ADDCOLUMNS (
                    SUMMARIZE (
                        dbo_jiraissue,
                        dbo_jiraissue[id],
                        "MAXID", CALCULATE (
                            MAX ( dbo_changestatus[ID] ),
                            dbo_changestatus[ChangDate] <= MaxDateView
                        )
                    ),
                    "STATUS", CALCULATE (
                        VALUES ( dbo_changestatus[NEWSTRING] ),
                        FILTER ( dbo_changestatus, dbo_changestatus[ID] = [MAXID] )
                    )
                ),
                [STATUS] = "In Progress"
            )
        ),
        ALL ( DimDate )
    )

Chart_In Progress = 
VAR MaxDateView =
    CALCULATE ( LASTDATE ( DimDate[Date] ) )
RETURN
    IF (
        MaxDateView = TODAY (),
        CALCULATE (
            DISTINCTCOUNT ( dbo_jiraissue[id] ),
            FILTER ( dbo_jiraissue, dbo_jiraissue[issuestatus] = "3" )
        ),
        CALCULATE (
            DISTINCTCOUNT ( dbo_changestatus[issueid] ),
            FILTER (
                ADDCOLUMNS (
                    SUMMARIZE (
                        FILTER ( ALL ( dbo_changestatus ), dbo_changestatus[ChangDate] <= MaxDateView ),
                        dbo_changestatus[issueid],
                        "MAXID", MAX ( dbo_changestatus[ID] )
                    ),
                    "STATUS", LOOKUPVALUE ( dbo_changestatus[NEWSTRING], dbo_changestatus[ID], [MAXID] )
                ),
                [STATUS] = "In Progress"
            ),
            ALL ( DimDate )
        )
    )

[tanquoc0309]

I try with LASTNONBLANK but the result is not correct 

[tanquoc0309]

I try with other formula and it worked.

Can anyone take a look my formula below and help to optimize it. thanks

Changed Status = 
VAR MAXDATEVIEW =
    CALCULATE ( LASTDATE ( DimDate[Date] ) )
VAR TEMPTABLE = 
            ADDCOLUMNS (
                SUMMARIZE (
                   Issue ,
                    Issue[id],
                    "MAXID", CALCULATE (
                        MAX ( IssueChangeCalculate[ID] ),
                        IssueChangeCalculate[ChangDate] <= MaxDateView
                    )
                ),
                "STATUS", 
                   CALCULATE (
                    VALUES ( IssueChangeCalculate[NEWSTRING] ),
                    FILTER ( IssueChangeCalculate, IssueChangeCalculate[ID] = [MAXID] ))
                )
            
RETURN
if(VALUES('Status'[Status]) = "Open",CALCULATE (
        DISTINCTCOUNT ( Issue[id] ),
        FILTER (
            TEMPTABLE,
            [STATUS] = "" ),FILTER(Issue,Issue[Created Date] <= MAXDATEVIEW
        ),  ALL ( DimDate )
    ),
 CALCULATE (
        DISTINCTCOUNT ( Issue[id] ),
        FILTER (
            TEMPTABLE,
            CONTAINS ( 'Status', 'Status'[Status_Satic], [STATUS] )
        ),  ALL ( DimDate )
    ))

[Anonymous]

HI @tanquoc0309,

Can you please share some some sample data or pbix for these to help us clarify for your scenario.

For optimize formula, you can also take a look at below link:

Optimizing duplicated DAX expressions using variables

Regards,

Xiaoxin Sheng

[tanquoc0309]

Hi @Anonymous

My scenario: I want to know the last status of each issue until the date and then count issue based on the status

I have 3 tables below:

My expectation:

Explaining: 

31-Jan:   2 issue created (Open) and no change this date -> 2 for Open and 0 for the rest status.

1-Feb:   Issue 1 (ID 1) changed to "In Progress" and Issue 2 (ID 2) still "Open" -> 1 for Open and 1 for In Progress

2-Feb: 

Issue 1: no change -> sill keep 1 for In Progress

Issue 2: Change to In Progress (ChangeID 5) then to Resolved (ChangeID 5) -> So Status of Issue 2 until 2-Fed: Resolved

=> 1 for In Progress and 1 for Resolved.

3-Feb, 4-Feb : No change -> still kip 1 In Progress and 1 Resolved

5-Feb:

Issue 1 goto Closed => 1 for Closed

Issue 2: no change -> 1 for resolved

==> 1 for closed & 1 for resolved.

Thanks & Best regards,

[Anonymous]

HI @tanquoc0309,

I also test with your sample data, but it seem like I can't add their nonexistent opened records.

I can only suggest to create crossjoin table with summary records.

Regards,

XIaoxin Sheng