Forum Discussion
Filter text value differences between date periods
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
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.
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
