Filter text value differences between date periods

[markjhillier]

Hi,

I'd like to be able to filter a table based on differences in the text value of equivalent fields across two different date periods.

For example, if I have a table containing item (text based) values over different periods:

Date	Item	Value
01/06/2018	1	No
01/06/2018	2	No
01/06/2018	3	No
01/07/2018	1	No
01/07/2018	2	Yes
01/07/2018	3	Yes

I'd want to be able to filter and return just the records that have different values from their equivalent records in a previous date period.  In the example above it would be the records changing from No to Yes (highlighted in bold)

If possible, I'd also like to be able to allow dynamic selection of the comparison periods, e.g. via a slicer.

Any help with this greatly appreciated.

Thanks,

Mark

[v-juanli-msft]

Hi @markjhillier

1. create a new data table and edit relationship with your table

Table = VALUES(Sheet1[Date])

2. create measures in your table

Measure 1 =
CALCULATE (
    DISTINCTCOUNT ( Sheet1[value] ),
    FILTER (
        ALLSELECTED ( Sheet1 ),
        [Date] <= MAX ( [Date] )
            && [item] = MAX ( [item] )
    )
)


Measure 2 = MAX('Table'[Date])

Measure 3 = MIN('Table'[Date])

Measure 4 = IF([Measure 3]<=MAX([Date])&&[Measure 2]>=MAX([Date]),1,0)

3. add [measure 4] to the visual level filter and set "show items when value is" 1

    add 'Table'[date] column to the slicer

 

Best Regards

Maggie

[markjhillier]

Hi @v-juanli-msft,

Thank you for replying with this suggestion and providing a pbix file.  It's very close to doing what I need, but there's something that's not quite as I'd like.

In the example we used - the outcome for item 2 is the issue: 

(Assuming the data filter is set from 1/6/2018 to 1/8/2018...) 

1/6/2018 - Item 2 correctly has measure 1 set to "1" - i.e. it hasn't changed since this is the first occurence in the time sequence.

1/7/2018 - Item 2 correctly has measure 1 set to "2" - i.e it has now changed to "yes" from original value "no".

1/8/2018 - Item 2 incorrectly has measure 1 set to "2" - i.e. even though the value changed back to "no", which is the same as the first occurence in 1/6/2018.  So ideally I'd like it to NOT consider this as a difference during this period, as the filtered start and end period value has remained the same - i.e. "no" (even though it may have changed at some point between).

I hope that makes sense?  Would you happen to know how your solution could be changed to factor that in?

Many thanks again for your help with this.

[markjhillier]

Hi @v-juanli-msft - I'm not sure if you saw my previous reply?  Would it be possible to help me with this final point?

Many thanks,

Mark

[Greg_Deckler]

I may have time to look at this in more detail, but it looks like you are going to need to use EARLIER in a measure.  See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...