Flag status change and show list

[dedmondson]

I have a table with various fields however for this purpose  consider the following fields only:

item # (text field)

Payment date (date field)

Status (text field)

A slicer exists to select on payment date (which are month ends)

I want to be be able to identify a list of Item# whose status changed relative to the prior period only as shown. So if I select for 8/31/2018 in the slicer I will only see those Items whose status code changed from their status as of 07/31/2018

how would I create a measure to determin/reflect this?

[v-yulgu-msft · Accepted solution]

Hi @dedmondson,

Create a measure, add it to visual level filter and set its value to 1.

check status =
IF (
    SELECTEDVALUE ( Table3[Status] )
        = CALCULATE (
            LASTNONBLANK ( Table3[Status], 1 ),
            FILTER (
                ALLEXCEPT ( Table3, Table3[item#] ),
                Table3[Payment date] < MAX ( Table3[Payment date] )
            )
        ),
    1,
    0
)

Best regards,

Yuliana Gu

[Greg_Deckler]

Sample data would help, but probably something along the lines of:

Measure =
VAR __paymentDate = MAX('Table'[Payment Date])
VAR __item = MAX('Table'[Item #])
VAR __status = MAX('Table'[Status])
VAR __lastDate = MAXX(FILTER('Table',[Item #]=__item && MONTH([Payment Date]) + 1 = MONTH(__paymentDate) && YEAR(__paymentDate)=YEAR([Payment Date])),[Payment Date])
VAR __lastStatus = MAXX(FILTER('Table',[Item #]=__item && [Payment Date] = __lastDate),[Status])
RETURN IF(__status <> __lastStatus,1,0)

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[dedmondson]

getting there I think...working on it... but you use the VAR expression,w hich one, there are 4 in the DAX measure builder?

Var.p

VAR.s

VARX.p

Varx.s

[Greg_Deckler]

Nope, just VAR. This creates a variable.