Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
longbored
Frequent Visitor

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

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_idrecord_idchange_fieldold_valuenew_valuechange_date
123452B4/13/174/20/176/21/16 22:14:43
123461B5/21/166/21/166/21/16 21:16:47
123471B6/21/167/21/166/21/16 21:31:35
123481A9/17/159/24/157/18/16 16:42:33
123492B4/20/174/1/1711/29/16 19:15:25
123502B4/1/176/30/1712/15/16 20:15:59
123512B6/30/1710/19/171/31/17 23:40:10
123522A4/14/164/1/171/31/17 23:44:15
123532A4/1/174/4/173/14/17 00:35:43
123542A4/4/174/11/173/20/17 15:15:30
123552B10/19/1710/12/176/8/17 17:51:10
123562B10/12/1710/26/177/17/17 20:58:30
123572B10/26/173/16/188/16/17 22:15:00
123582B3/16/184/19/1810/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_idchange_fieldinitial_valuelatest_valuetotal_changes
1A9/17/159/24/151
2A4/14/164/11/173
1B5/21/167/21/162
2B6/30/174/20/174

 

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!

5 REPLIES 5
dk_dk
Super User
Super User

Hi @longbored ,

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





v-yuezhe-msft
Microsoft Employee
Microsoft Employee

@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]))
1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.