Slicer-based History View - Initial/Latest Value, number of changes

[longbored]

I'm trying to represent date changes (from a history table) over a user-specifiable period of time. I'd like to be able to show not only the number of changes that have occured in the time period, but also the original value from that time period, and what the last value is (also in that period).

Although the input data is currently formatted like this, I can modify the format if it gets me any further:

change_id	record_id	change_field	old_value	new_value	change_date
12345	2	B	4/13/17	4/20/17	6/21/16 22:14:43
12346	1	B	5/21/16	6/21/16	6/21/16 21:16:47
12347	1	B	6/21/16	7/21/16	6/21/16 21:31:35
12348	1	A	9/17/15	9/24/15	7/18/16 16:42:33
12349	2	B	4/20/17	4/1/17	11/29/16 19:15:25
12350	2	B	4/1/17	6/30/17	12/15/16 20:15:59
12351	2	B	6/30/17	10/19/17	1/31/17 23:40:10
12352	2	A	4/14/16	4/1/17	1/31/17 23:44:15
12353	2	A	4/1/17	4/4/17	3/14/17 00:35:43
12354	2	A	4/4/17	4/11/17	3/20/17 15:15:30
12355	2	B	10/19/17	10/12/17	6/8/17 17:51:10
12356	2	B	10/12/17	10/26/17	7/17/17 20:58:30
12357	2	B	10/26/17	3/16/18	8/16/17 22:15:00
12358	2	B	3/16/18	4/19/18	10/9/17 19:36:18

If this was pure SQL (via SSRS, or something like that), I can use couple of window functions and derived results to easily rank/filter/etc the results upon each execution. But, this is Power BI and DAX.

Ideally, when the user selects/modifies the date range (in this case, I've acted like I selected 6/1/2016 to 6/1/2017), I could get results like this:

record_id	change_field	initial_value	latest_value	total_changes
1	A	9/17/15	9/24/15	1
2	A	4/14/16	4/11/17	3
1	B	5/21/16	7/21/16	2
2	B	6/30/17	4/20/17	4

I don't even know if something like this is possible to do on the fly in Power BI. I've toyed with setting fixed date ranges, and only letting the user select on of those (which would let me cheat and I could pre-calculate the results, and then make the visual filter reference that pre-calculated range's results). But that's a distant second options in my mind - I'd love for the user to dynamically select whatever range they want. Ideas? Help? Thanks in advance!

[dk_dk]

Hi @longbored ,

I am facing a similar issue in principe - did you ever find a working solution?

[v-yuezhe-msft]

@longbored,

Do you use change_date field to create a slicer and select date range(6/1/2016-6/1/2017) in the slicer to get expected result? If so, it seems that you input wrong initial_value and latest_value for the last record(record_id=2, and change_field=B). I create the following measures in the table and get result as shown in the screenshot below.

initial_value = MIN(Table1[old_value])
latest_value = MAX(Table1[new_value])
total changes = CALCULATE(COUNTROWS(Table1),ALLEXCEPT(Table1,Table1[record_id],Table1[change_field],Table1[change_date]))


Regards,
Lydia

[longbored]

Lydia, thanks for the response. The result set I showed was calculated in SQL, and I'm hoping to replicate in Power BI.

The problem is a bit more complicated than a traditional min/max, because I don't need the min/max from old/new_value (respectively). I need the old_value associated with the MIN(change_date), as well as the new_value associated with the MAX(change_date). To further complicate it (and potentially to see how/why window functions work so well with this in SQL), it can't just be the min/max from across the dataset - it needs to be the min/max associated with the same record_id and change_field. Additionally, I'd like the row count of items across the same record_id and change_field within the correct change_date... though this seems far easier.

[parry2k]

try following:

step 1 - add new column

Change Number = Rankx(filter(History, History[record_id] = EARLIER(History[record_id]) && History[change_field] = EARLIER(History[change_field])), History[change_date],,ASC, dense)

step 2 - Add new measure to get # of changes:

Count of Changes = 
var totalChanges =  Calculate(max(History[Change Number]), AllExcept(history, History[record_id], History[change_field])) 
return totalChanges-1

step 3 - add new measure to get initial value

Initial Value = Calculate(FIRSTDATE(History[old_value]), Filter(AllExcept(history, History[record_id], History[change_field]), History[Change Number] = 1))

step 4 - add new measure to get latest value

Latest Value = Calculate(FIRSTDATE(History[new_value]), Filter(AllExcept(history, History[record_id], History[change_field]), History[Change Number] = MAX(History[Change Number])))

add table visual and drop these in values:

Record Id

Change field

Count of changes

Initial Value

Latest Value

[longbored]

parry2k, I appreciate the response. Unfortunately, your solution doesn't work. The initial/latest values work correctly only on the data set as a whole, but don't recalculate when the slicer is applied to the change_date field. Ideas?