topic Re: Status Tracking in DAX Commands and Tips

Status Tracking

Euro0681 — Mon, 22 May 2023 14:43:58 GMT

Hi All,
Needing some help on displaying the orders where the status has changed when compared to the previous day.

Table on the left showing 2 Order ID's and the table on the right only returns the order where the status has changed when compared to "Todays DT" Column.

Essentially Order ID '123' was in progress and expected to be delivered on the 20th, "Todays DT" Column is just tracking the order everyday hence making an entry everyday. But when the 20th of May came we can see the order was not delivered and had been pushed back a couple of days making it "Late" and is displayed in the table on the right.

How could I replicate this using a DAX? Either measure/column is fine just hitting a roadblock.

Re: Status Tracking

tamerj1 — Mon, 22 May 2023 16:28:39 GMT

Hi Euro0681

Why not just filter for "Late" status?

Re: Status Tracking

Euro0681 — Mon, 22 May 2023 18:07:01 GMT

So what I'm getting at is using the MAX(Today's DT) and compare each order with previous Today's Date < MAX(Todays DT) and check to see if anything on the order has changed (Status in this example).
Hope this image clears it up

Re: Status Tracking

tamerj1 — Tue, 23 May 2023 18:07:58 GMT

Euro0681

Place the following measure in the filter pane of the table visual, select "is not blank" and apply the filter.
FilterMeasure =
VAR T1 =
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Order ID] ) )
VAR T2 =
DISTINCT (
SELECTCOLUMNS (
T1,
"@Status", 'Table'[Status],
"@Expected", 'Table'[Expected DT]
)
)
VAR T3 =
SELECTCOLUMNS (
TOPN ( 1, T1, 'Table'[Tody's DT] ),
"@Status", 'Table'[Status],
"@Expected", 'Table'[Expected DT]
)
VAR T4 =
SELECTCOLUMNS (
'Table',
"@Status", 'Table'[Status],
"@Expected", 'Table'[Expected DT]
)
RETURN
IF ( COUNTROWS ( T2 ) > 1, COUNTROWS ( INTERSECT ( T3, T4 ) ) )