Count quantity of months by last status

[MagnoDemarqui]

Hi Community!

I'm trying to count how much months (or times) a product is being showned on my database, based on the last status.
I already get a way to isolate the last status on a variable, but I'm not knowing how to count how much times this status appeared consecutively.
My actual var to isolate the last status is this:  

VAR Last_Status = CALCULATE(Count(Table'[Status),LASTDATE('Table'[Date])

Do someone know how to achieve this count?

My table is:

Product	Date	Status
Shampoo	01/01/2023	OK
Soap	01/01/2023	OK
Toothpaste	01/01/2023	Out of stock
Shampoo	01/02/2023	Out of stock
Soap	01/02/2023	OK
Toothpaste	01/02/2023	OK
Shampoo	01/03/2023	Out of stock
Soap	01/03/2023	Out of stock
Toothpaste	01/03/2023	OK

The result of the expected count:

Product	Status	Months
Shampoo	Out of Stock	2
Soap	Out of Stock	1
Toothpaste	OK	2

[johnt75 · Accepted solution]

You can create a couple of measures like

Last status = IF(
	ISINSCOPE( 'Table'[Product] ),
	SELECTCOLUMNS(
		INDEX(
			1,
			'Table',
			ORDERBY( 'Table'[Date], DESC ),
			PARTITIONBY( 'Table'[Product] ),
			MATCHBY( 'Table'[Product], 'Table'[Date] )
		),
		"@value", 'Table'[Status]
	)
)

Consecutive same status = IF(
	ISINSCOPE( 'Table'[Product] ),
	VAR LastEntry =
		INDEX(
			1,
			'Table',
			ORDERBY( 'Table'[Date], DESC ),
			PARTITIONBY( 'Table'[Product] ),
			MATCHBY( 'Table'[Product], 'Table'[Date] )
		)
	VAR LastStatus =
		SELECTCOLUMNS( LastEntry, "@value", 'Table'[Status] )
	VAR LastStatusDate =
		SELECTCOLUMNS( LastEntry, "@value", 'Table'[Date] )
	VAR PrevEntry =
		INDEX(
			1,
			FILTER(
				'Table',
				'Table'[Date] < LastStatusDate
					&& 'Table'[Status] <> LastStatus
			),
			ORDERBY( 'Table'[Date], DESC ),
			PARTITIONBY( 'Table'[Product] ),
			MATCHBY( 'Table'[Product], 'Table'[Date] )
		)
	VAR PrevDate =
		SELECTCOLUMNS( PrevEntry, "@value", 'Table'[Date] )
	VAR Result = DATEDIFF( PrevDate, LastStatusDate, MONTH )
	RETURN
		Result
)

[MagnoDemarqui]

Hello @johnt75, thanks for your reply!

I've tested the second measure, and it worked when I put product and the measure in a table, but when I tryied to add the status it turned all blank

In this visualization I will really need to show the status and also make possible to filter the dates, do know some way?

[johnt75]

You can use the first measure to show the status. It should also work with date filters or slicers.