Forum Discussion

FU's avatar
FU
Helper IV
3 years ago

Tracking data changes on monthly basis

Hi all   I am trying to create a table or matrix on Power BI where I can visually track any changes to a spreadsheet I have connected to SharePoint.   Each month an updated file will be placed in...
Need Help
Adamboer's avatar
Adamboer
Responsive Resident
3 years ago

To create a table or matrix in Power BI that tracks changes to a spreadsheet connected to SharePoint, you can follow these steps:

  1. Connect to the SharePoint folder where the spreadsheets are stored in Power BI Desktop.

  2. Import the spreadsheet into Power BI by selecting the appropriate file and sheet.

  3. In the "Fields" pane, drag the project column to Rows, and drag the spend column to Values.

  4. In the "Visualizations" pane, select either "Table" or "Matrix" as per your preference.

  5. Drag the "Month" column to Columns.

  6. You should now see a table or matrix with the spend values for each project for each month. To see the difference in spend between months, you can add a calculated column using the "New Column" option in the "Modeling" tab.

  7. In the formula bar, enter the following formula:

    Monthly Spend Difference = CALCULATE(SUM(Sheet1[Spend]), FILTER(Sheet1, Sheet1[Project] = EARLIER(Sheet1[Project])))

    This formula will calculate the difference in spend between the current month and the previous month.

  8. Add the "Monthly Spend Difference" column to the table or matrix and format it as needed.

Now you should have a table or matrix that shows the spend values for each project for each month and the difference in spend between the current and previous month.