Forum Discussion
[UNSOLVED] Track changes between multiple tables
Hey all,
I have a sharepoint folder which contains weekly snapshots of my Excel data file.
I would like to be able to compare each file against one another and show where there are changes.
E.g. if a client wants to view what changes have been made to financial data from week 1 and week 3, they can select week 1 and week 3 filters and using conditional formatting (or some method) see if there are any changes in that column
Image 1: This is how I capture my weekly snapshots
Image 2: I then connect my Power BI dashboard to my SharePoint folder and read all the files as shown below.
Image 3: Now for the visual part, I want to be able to select 2 reports/dates (as shown in the filter) and immediately see where there are changes between the 2 reports. The current view shows the full table from week 1 and week 3 but I'm only interested in seeing where there has been changes.
Any suggestions would be hugely appreciated!
Hi FU ,
//I want to be able to select 2 reports/dates
Using a slicer to switch tables seems difficult to do.
My idea is to group the tables by table's name(Week1..Week2) and then append the query as one table. Then create two unconnected Dim table's name tables for the two slicers. Then new a measure like:Measure = VAR _slicer1 = SELECTEDVALUE('Table 2'[Name]) VAR _slicer2 = SELECTEDVALUE('Table 3'[Name]) VAR _pjid = SELECTEDVALUE('Table'[Project ID]) VAR _forecast1 = CALCULATE(MAX('Table'[Forecast]),'Table'[Name]=_slicer1) VAR _forecast2 = CALCULATE(MAX('Table'[Forecast]),'Table'[Name]=_slicer2) VAR _status1 = CALCULATE(MAX('Table'[Status]),'Table'[Name]=_slicer1) VAR _status2 = CALCULATE(MAX('Table'[Status]),'Table'[Name]=_slicer2) RETURN IF(_forecast1<>_forecast2||_status1<>_status2,1)result:
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
3 Replies
Calling all experts!
Let's take the below table as an example. Let's assume every week I run a flow to automatically grab a snapshot of the current data in my Excel file and store these files into a SharePoint folder which is connected to my PBI file.
Example -
The week 1 table is showing my data at a given snapshot date.
Week 1:
Project Name Project ID Programme ID Forecast Status A ProjectID 1 ProgrammeID 1 £500,000.00 Status A B ProjectID 2 ProgrammeID 2 £500,000.00 Status B C ProjectID 3 ProgrammeID 3 £500,000.00 Status C D ProjectID 4 ProgrammeID 4 £500,000.00 Statuc D One week later my power flow takes a snapshot of my Excel file and stores Week 2 into my SharePoint folder
Week 2: (changes from week 1 and week 2 in 'Forecast' and 'Status' columns.
Project Name Project ID Programme ID Forecast Status A ProjectID 1 ProgrammeID 1 £500,000.00 Status A B ProjectID 2 ProgrammeID 2 £640,000.00 Status H C ProjectID 3 ProgrammeID 3 £700,000.00 Status G D ProjectID 4 ProgrammeID 4 £500,000.00 Statuc O After several weeks I would have multiple snapshots of this table with data changing each week. I want to create a visual which allows me to see which field have been changed. Something like the below image:
If i select week 1 and week 3 in my filters, the column+row which have changes in them would be highlighted.
Hi FU ,
//I want to be able to select 2 reports/dates
Using a slicer to switch tables seems difficult to do.
My idea is to group the tables by table's name(Week1..Week2) and then append the query as one table. Then create two unconnected Dim table's name tables for the two slicers. Then new a measure like:Measure = VAR _slicer1 = SELECTEDVALUE('Table 2'[Name]) VAR _slicer2 = SELECTEDVALUE('Table 3'[Name]) VAR _pjid = SELECTEDVALUE('Table'[Project ID]) VAR _forecast1 = CALCULATE(MAX('Table'[Forecast]),'Table'[Name]=_slicer1) VAR _forecast2 = CALCULATE(MAX('Table'[Forecast]),'Table'[Name]=_slicer2) VAR _status1 = CALCULATE(MAX('Table'[Status]),'Table'[Name]=_slicer1) VAR _status2 = CALCULATE(MAX('Table'[Status]),'Table'[Name]=_slicer2) RETURN IF(_forecast1<>_forecast2||_status1<>_status2,1)result:
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
