COUNTROWS on last record that matches a status prior to date slicer

[Agreenwood]

Hi,

Please consider the following fact table:

user_id	date	status
1	01 January 2020	A
1	01 February 2020	B
1	01 March 2020	A
2	01 January 2020	B
2	01 February 2020	A
2	01 March 2020	C
2	01 April 2020	B
3	01 January 2020	D
3	01 February 2020	B
3	01 March 2020	C
3	01 April 2020	A
4	01 January 2020	B
4	01 February 2020	A
4	01 March 2020	B
4	01 April 2020	B

Now please consider a date slicer on a report set to "March 2020".

I would like to create a measure which returns the number of records where the last record for each user_id prior to the slicer date matches status "A". In the example above, with the date set to March 2020, then the measure would return 2 as both user_id 2 and 4 match the conditions.

In my attempts to get this to work I have used GROUPBY along with MAXX within a VAR RETURN measure to get the last date for each user_id prior to the date slicer, though I'm now unable to return the status for last record found.

Any help is greatly appreciated, and I hope I have explained my question clearly.

Edit: example PBIX file link here: Example PBIX File 

[Anonymous · Accepted solution]

// Assumptions:
// You have a Dates table that should be disconnected
// from your fact table (let's call it T). For each
// user_id the combinations of (date, status) must
// be unique.

// Once you've made a selection in the
// slicer (Dates[Date])...

[# Records] =
var __lastVisibleDate = MAX( Dates[Date] )
return
    SUMX(
        VALUES( T[user_id] ),
        var __lastStatus =
            MAXX(
                TOPN(1,
                    CALCULATETABLE(
                        T,
                        // You may want to use <
                        // instead of <=
                        T[date] <= __lastVisibleDate
                    ),
                    T[Date],
                    DESC
                ),
                T[status]
            )
        return
            if( __lastStatus = "A", 1 )
    )
    
// Bear in mind that this measure is
// responding to slicing, so you should
// carefully examine why the result is
// what it is.

[amitchandak]

@Agreenwood , assumed you have date table joined with your date

have this measure with user_id in visual

measure =
var _max = eomonth(maxx(allselected(Date),Date[Date]),-1)
return
countx(filter(summarize(Table, table[user_id] ,"_1", calculate(lastnonblankvalue(Table[date],maxx(Table[status])), filter(all(Date),Date[Date]<_max))),[_1]="A"),[user_id])

You can try with date of your table too

[Agreenwood]

Thanks @amitchandak , this looks as though it is really close, though I think it is excluding user_id's who do not have a record in the month selected.

For example, using your measure and the same data table as before with the date set to "April 2020", then the measure returns BLANK. I would like it to return 1, since user_id 1's last status prior to April is A despite him not having a record in April.

Is there a way of adapting the measure to accomodate this?