Determine the latest status and Count ID based on date slicer

[Anonymous]

Hey guys,

I have a table with 3 columns. ID, Date Stamp and Status_Change. I am working on a dashboard with a date slicer and several cards which counts how many IDs are in a certain status based on the dates selected in the date slicer.

ID	Date_Stamp (M/D/Y)	Status
001	1/1/2020	Open
001	1/2/2020	Escalated
001	1/3/2020	Closed
002	1/2/2020	Open
002	1/4/2020	Closed
003	1/3/2020	Open
003	1/4/2020	Escalated
004	1/1/2020	Open
005	1/4/2020	Open
005	1/6/2020	Escalated

If I set the max date slicer date to 1/7/2020, Im trying to get each status card to output the number of of IDs whose latest status equals to it.

Open = 1

Escalated = 2

Closed = 2

So far I tried the following formula but its counting all the rows with that status (Whereas Im hoping for it to only count it if the latest status is equal to it.)

Measure = CALCULATE(COUNTROWS(FILTER('Table', 'Table'[Date_Stamp] <= CALCULATE( max('Table'[Date_Stamp]), ALLEXCEPT('Table','Table'[ID])))),'Table'[Status]="Open")

Thanks in advance for the help. 🙂

[Anonymous]

// Assumption:
// There is a Date table in the model
// that joins to the fact table on
// [Date]. Fact table is called 'T'.
// There's also a dimension called Status
// that joins to 'T' on [Status]. Status
// has only one column - Status.
// All columns in 'T' must be hidden, so
// that slicing only takes place via dimensions.
// The above are the principles of
// good dimensional design, therefore you
// should follow them.


[# Open] =
var __maxDate = MAX( Dates[Date] )
var __idsWithLatestDate =
	CALCULATETABLE(
	
		ADDCOLUMNS(
			VALUES( T[ID] ),
			"@MaxDate",
				CALCULATE( MAX( T[Date_Stamp] ) )
		),
		
		Dates[Date] <= __maxDate,
		ALL( T )
	)
var __result =
	CALCULATE(
		DISTINCTCOUNT( T[ID] ),
		TREATAS(
			__idsWithLatestDate,
			T[ID],
			Dates[Date]
		),
		// Change the status to
		// Escalated or Closed
		// to get the corresponding
		// measures. Don't forget to
		// change the measure name
		// as well.
		Status[Status] = "Open"
		ALL( T )
	)
return
	__result

Best

D

[mahoneypat]

Here is another way to do it.  You can use this measure in your Open Count card, and can just replace the status in two more cards.  This has a Date table that is used in the slicer for Max Date.

OpenCount =
VAR slicerdate =
SELECTEDVALUE ( 'Date'[Date] )
VAR summarytable =
ADDCOLUMNS (
ADDCOLUMNS (
VALUES ( 'Status'[ID] ),
"MaxDate", CALCULATE (
MAX ( 'Status'[Date_Stamp (M/D/Y)] ),
ALL ( 'Status' ),
'Status'[Date_Stamp (M/D/Y)] <= slicerdate,
'Status'[ID] = EARLIER ( 'Status'[ID] )
)
),
"LatestStatus", CALCULATE (
MIN ( 'Status'[Status] ),
'Status'[ID] = EARLIER ( 'Status'[ID] ),
'Status'[Date_Stamp (M/D/Y)] = EARLIER ( [MaxDate] )
)
)
RETURN
COUNTROWS ( FILTER ( summarytable, [LatestStatus] = "Open" ) )

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

@mahoneypat 

I found a post where the person gets two similar columns and gets the answer in a tablular form (code written below), but dont have the expertise to convert it into a measure for a card. 

max date = var mind=MINX(ALLSELECTED('calendar'),'calendar'[Date]) var maxd=MAXX(ALLSELECTED('calendar'),'calendar'[Date]) return CALCULATE(MAX('Table 2'[dateentered]), FILTER(ALL('Table 2'), 'Table 2'[id]=MIN('Table 2'[id]) && 'Table 2'[dateentered]>=mind && 'Table 2'[dateentered]<=maxd))

max value = LOOKUPVALUE('Table 2'[value],'Table 2'[dateentered],[max date])

Hope this helps.

[mahoneypat · Accepted solution]

The version I sent had not relationship with the Date table and was single select.  This one works when the relationship is there and for a range of dates.  The EARLIER brings back the value for the current row in the virtual table.

OpenCount =
VAR slicerdates =
VALUES ( 'Date'[Date] )
VAR summarytable =
ADDCOLUMNS (
ADDCOLUMNS (
VALUES ( 'Status'[ID] ),
"MaxDate", CALCULATE (
MAX ( 'Status'[Date_Stamp (M/D/Y)] ),
ALL ( 'Status' ),
VALUES ( 'Date'[Date] ),
'Status'[ID] = EARLIER ( 'Status'[ID] )
)
),
"LatestStatus", CALCULATE (
MIN ( 'Status'[Status] ),
'Status'[ID] = EARLIER ( 'Status'[ID] ),
'Status'[Date_Stamp (M/D/Y)] = EARLIER ( [MaxDate] )
)
)
RETURN
COUNTROWS ( FILTER ( summarytable, [LatestStatus] = "Open" ) )

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Thanks @mahoneypat. That worked perfectly. I did have to change the time defining DAX syntax to MAX instead of VALUES ( as I was using a between date slicer but other than that, Im truly grateful for your help with this.

[Anonymous]

Hey @mahoneypat, 

Thanks for the response. I tried your solution but, I am getting  (blank) as the answer when I put the measure in a card. Also, I am using a date between, not selecting on one date. Im trying to play around with the formula and see if something sticks. 

Also, Im curious to know why you are using EARLIER instead of MAX in the solution? Thanks once again

[nandukrishnavs]

@Anonymous 

Try this measure

Measure =
VAR _table =
    SUMMARIZE (
        MyTable,
        MyTable[ID],
        "Open", COUNTROWS (
            FILTER (
                ALL (
                    MyTable[ID],
                    MyTable[Status]
                ),
                MyTable[ID]
                    = EARLIER ( MyTable[ID] )
                    && MyTable[Status] = "Open"
            )
        ),
        "Closed", COUNTROWS (
            FILTER (
                ALL (
                    MyTable[ID],
                    MyTable[Status]
                ),
                MyTable[ID]
                    = EARLIER ( MyTable[ID] )
                    && MyTable[Status] = "Closed"
            )
        ),
        "Escalated", COUNTROWS (
            FILTER (
                ALL (
                    MyTable[ID],
                    MyTable[Status]
                ),
                MyTable[ID]
                    = EARLIER ( MyTable[ID] )
                    && MyTable[Status] = "Escalated"
            )
        )
    )
VAR _closed =
    COUNTROWS (
        FILTER (
            _table,
            [Closed]
                <> BLANK ()
        )
    )
VAR _escalated =
    COUNTROWS (
        FILTER (
            _table,
            [Closed]
                = BLANK ()
                && [Escalated]
                    <> BLANK ()
        )
    )
VAR _open =
    COUNTROWS (
        FILTER (
            _table,
            [Closed]
                = BLANK ()
                && [Escalated]
                    = BLANK ()
                && [Open]
                    <> BLANK ()
        )
    )
RETURN
    _open
/* you can return _closed/_escalted/_open */

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Anonymous]

Hey @nandukrishnavs 

Thanks for the response. I was looking at your solution, but dont see the date table being used. If there a specific reason for it?

[nandukrishnavs]

@Anonymous I assume that the status will follow an order - open, escalated, closed. Also the measure will filter based on the date range selection. Please try that and let me know your comments.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂