Counting records based on dynamic variable

Hi Anonymous ,

I doubt the order for From-To type could be reversed, such as from partial application to full application, or it also can be from full application to partial application...

So in order to consider comprehensive, I use CROSSJOIN() to create a FromTo table as shown below:

FromTo =
VAR _from =
    DISTINCT ( SELECTCOLUMNS ( 'Table', "From", 'Table'[Status_changed_to] ) )
VAR _to =
    DISTINCT ( SELECTCOLUMNS ( 'Table', "To", 'Table'[Status_changed_to] ) )
RETURN
    CROSSJOIN ( _from, _to )

Now please follow these steps:

1. Find the first status

From =
VAR _firstdate =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Modification_datetime], TRUE () ),
        ALLEXCEPT ( 'Table', 'Table'[Application id] )
    )
RETURN
    LOOKUPVALUE ( 'Table'[Status_changed_to], [Modification_datetime], _firstdate )

2. Find the last status

To =
VAR _lastdate =
    CALCULATE (
        LASTNONBLANK ( 'Table'[Modification_datetime], TRUE () ),
        ALLEXCEPT ( 'Table', 'Table'[Application id] )
    )
RETURN
    LOOKUPVALUE ( 'Table'[Status_changed_to], [Modification_datetime], _lastdate )

3. Create a measure to count the number of application with each From - To type:

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Application id] ),
    FILTER (
        'Table',
        'Table'[From] = MAX ( 'FromTo'[From] )
            && 'Table'[To] = MAX ( 'FromTo'[To] )
    )
)

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.