Forum Discussion

FU's avatar
FU
Helper IV
3 years ago
Solved

[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. ...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    3 years ago

    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 Team

     

    If 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

FU's avatar
FU
Helper IV
3 years ago

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 NameProject IDProgramme IDForecastStatus
AProjectID 1ProgrammeID 1£500,000.00Status A
BProjectID 2ProgrammeID 2£500,000.00Status B
CProjectID 3ProgrammeID 3£500,000.00Status C
DProjectID 4ProgrammeID 4£500,000.00Statuc 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 NameProject IDProgramme IDForecastStatus
AProjectID 1ProgrammeID 1£500,000.00Status A
BProjectID 2ProgrammeID 2£640,000.00Status H
CProjectID 3ProgrammeID 3£700,000.00Status G
DProjectID 4ProgrammeID 4£500,000.00Statuc 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.