Count of Projects from Min Stage to Max Stage Display in Matrix Visual

[Anonymous]

Hi Everyone,

I am trying to create matrix visual based on my sample data for project transition, based on date of status change selection my projects from stage and to stage should be changed and always I should showcase latest stage for project

Sample Input:

Expected Output

When I Select Date From 11-16-2021 to 5-25-2022 or after

When I Select Date From 11-16-2021 to 5-24-2022

Always project status transition is from Latest From Status to Latests To Status.

Thanks Advance for your help!

[Anonymous]

Previous Status-Rows
Status-Columns

[tamerj1]

@Anonymous 

Interesting problem. So the previous status is always the first one and the status is always the last one? This won't work using the columns from the same table. It would require two duplicate tables having all unique status values, one to be placed on rows and one tobe placed on columns. Yet it will remain tricky. I will try to work on it tomorrow. 

[Anonymous]

Thanks @tamerj1 , for checking on it, Yes we need to showcase as columns but the challenge here is based on date selection it should show case min status->max status transition and count against status with measures I could able to achive but ask from client is to showcase as matrix, pls let me know if any findings 
Thanks Again for checking...

[tamerj1]

Hi @Anonymous 
Not sure if this is what you need. Please refer to attached sample file

Count = 
SUMX ( 
    VALUES ( Data[ID] ),
    CALCULATE ( 
        VAR SelectedStatus = SELECTEDVALUE ( 'Status'[Status] )
        VAR SelectedPreStatus = SELECTEDVALUE ( Previous_Status[Previous Status] )
        VAR CurrentIDTable = CALCULATETABLE ( Data, ALLSELECTED ( 'Date' ) )
        VAR LastStatusDate = MAXX ( CurrentIDTable, Data[Date of Status Change] )
        VAR LastStatus = MAXX ( FILTER ( CurrentIDTable, Data[Date of Status Change] = LastStatusDate ), Data[Status] )
        VAR FirstPreStatusDate = MAXX ( TOPN ( 2, CurrentIDTable, Data[Date of Status Change], ASC ), Data[Date of Status Change] )
        VAR FirstPreStatus = MAXX ( FILTER ( CurrentIDTable, Data[Date of Status Change] = FirstPreStatusDate ), Data[Previous Status] )
        RETURN 
            IF (
                SelectedStatus = LastStatus && SelectedPreStatus = FirstPreStatus,
                1,
                0
            ) 
    )
)

[Anonymous · Accepted solution]

Hi @tamerj1 
Thanks for looking into it, I did some changes in KPI logic, here is logic which is working as expected for me

Assigned =

VAR _FromDate=SELECTEDVALUE('From Date'[Date])

VAR _ToDate=SELECTEDVALUE('To Date'[Date])

VAR _Status=SELECTEDVALUE('Status'[Status])

VAR _FromId=CALCULATETABLE(VALUES('Deal Pipeline'[ID]),'Deal Pipeline'[Date]=_FromDate)

VAR _ToID=CALCULATETABLE(VALUES('Deal Pipeline'[ID]),'Deal Pipeline'[Date]=_ToDate

&& 'Deal Pipeline'[Status]="Assigned")

VAR _Except=INTERSECT(_FromId,_ToID)

VAR _Result=

COALESCE(CALCULATE(COUNTROWS(_Except)),0)

RETURN

_Result

Created 2 Date Tables and Joined to DealPipeline Fact based on Date Column.

Computed Date Column in Power Query like below
Table.AddColumn(#"Added Custom", "Date", each {Number.From([From DAte])..Number.From([To_Date])})

[tamerj1]

Hi @Anonymous 

which column is in the rows and which colum is in the columns of the matrix?