Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Hi @longbored ,
I am facing a similar issue in principe - did you ever find a working solution?
Proud to be a Super User! | |
@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
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.
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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
85 | |
67 | |
49 |
User | Count |
---|---|
140 | |
113 | |
104 | |
64 | |
60 |