Interactive dashboard for the data

Question

want to create an interactive dashboard. this is an example of data and the line can go upto 100000 lines.

We currently present with values, now looking to present in terms of graphical presentations with following Flux:

a Month on Month : Say, if we are checking for Mar 26 then Flux will be with Feb 26

b Quarter on Quarter : Say, if we are checking for Jan 26 which Q1 26, then the flux to be only for Oct 25 Q4 25 and if we are doing for Jan and Feb 26 then it should be Oct and Nov 25

c Year on Year: Say, if we are checking for Mar 26 then Flux will be with Mar 25

d Region wise: a region wise chart based on Month on Month criteria

e Region wise: a region wise chart based on Quarter on Quarter criteria

f Region wise: a region wise chart based on Year on Year criteria

e Function wise: a function wise chart based on Quarter on Quarter criteria.

Each graph must have GL at X axis & Values at Y axis and Difference with a bar graph.

Below is the SharePoint link for the sample data base.

Answer 1

Hi @Abdul Razack,

According to your request, you can try following these steps (the guidance is quite long so you need to follow and re-check carefully):

Step 1: Verify the source data

For proper MoM/QoQ/YoY calculations, you need a real Excel date on the Month + Year column. If your Month column is text like Jan 2026, Feb 2026, etc. you need to convert them into real date (if it's real date already, skip to step 2):

• On the blank column, use this formula =DATEVALUE("1"& MonthDataRange) and format it as mmm yyyy to create a real date value then copy and paste value (Ctrl+Shift+V) them back into the Month column
• Name your Source data table (e.g. FactDep)

Step 2: Prepare Data Model tables

Next you need a separate Calendar Table:

• Create a new worksheet name Calendar and add these headers: Date, Year, Month No, Month Name, Month-Year, Quarter, Quarter-Year
• Go back to your Source data table, in a blank column, use this formula =UNIQUE(FactDep[Month]) to extract all the Month value then copy > paste value (Ctrl+Shift+V) into the Date column of Calendar sheet and set the formatting into Date.
• Now use these formulas:
  • Year column: =YEAR(A2)
  • Month No column: =MONTH(A2)
  • Month Name: =TEXT(A2, "mmm")
  • Month-Year: = TEXT(A2, "mmm yyyy")
  • Quarter: ="Q"&ROUNDUP(MONTH(A2)/3,0)
  • Quarter-Year: ="Q"&ROUNDUP(MONTH(A2)/3,0)&" "&YEAR(A2)
• Convert the range to Table and name it "Calendar"

Step 3: Analyze with Data Model

• If you don't see the PowerPivot tab on the ribbon, go to File > Options > Customize Ribbon > check the box next to PowerPivot to enable it.
• Click anywhere on the FactDep table > PowerPivot tab > Add to Data Model
• Go back to the Excel file > click anywhere on the Calendar table > PowerPivot tab > Add to Data Model. In the end, you must have two tables inside the Data Model like this

• In PowerPivot, go Design > Create Relationship > set up like below and press OK

• Go back to Home tab > View > select Calculation Area > you will see a field below a grey line
• Select a cell in the Calculation Area and add these DAX measures one-by-one:
  • TotalDep:=SUM(FactDep[Total Dep])
  • PrevMonthDep:=CALCULATE([TotalDep],DATEADD('Calendar'[Date],-1,MONTH))
  • MoMFlux:=[TotalDep]-[PrevMonthDep]
  • PrevQuarterDep:=CALCULATE([TotalDep],DATEADD('Calendar'[Date],-1,QUARTER))
  • QoQFlux:=[TotalDep]-[PrevQuarterDep]
  • PrevYearSameMonth:=CALCULATE([TotalDep],DATEADD('Calendar'[Date],-1,YEAR))
  • YoYFlux:=[TotalDep] - [PrevYearSameMonth]

• Then you can close the PowerPivot window now.

Step 4: Build the PivotTable and PivotChart

I will demonstrate an example for GL Month on Month flux, and you can perform similar to other charts

• Go to Insert > PivotTable > From Data Model
• Put the main calculation fx TotalDep into Values field
• For GL Month on Month flux, we will need GL of FactDep table in Rows, in values add two additional fx PrevMonthDep and fx MoMFlux
• Select the PivotTable and go to PivotTableAnalyze > PivotChart > choose Clustered Bar since you want GL of Y axis and Totals in X axis.
• You can add slicers/filters based on your preferences

• The other 4 PivotTables have the same approach, e.g. for Region MoM Flux, add Region in axis and fx PrevMonthDep and fx MoMFlux into values

Please try the steps and let me know if it works. If you have any questions or need further assistance, please feel free to share them in the comments on this post so I can continue to support you.    

Thank you for your understanding and cooperation. I'm looking forward to your reply.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

Answer 2

Hi @Abdul Razack,

To better understand the issue, I have initiated a private message session to ask for more details since the information includes your personal information. When you have a moment, please click on “View message” at the top of this thread to open the private conversation and review the document.

Thank you for your patience and understanding throughout this process. I'm looking forward to your response.

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

Answer 3

Use Excel PivotTables, PivotCharts, Slicers, and Timelines to build the interactive dashboard and support MoM/QoQ/YoY and region/function views from a large dataset.

1. Prepare the data

• Store all 100,000+ rows in a single Excel table (Insert > Table).
• Ensure fields include at least: GL, Value, Date, Region, Function (and any other needed dimensions).

2. Create a base PivotTable
3. Select any cell in the data table.
4. Go to Insert > PivotTable and place the PivotTable on a new worksheet.
5. In the PivotTable Fields pane:
   • Put GL in Rows.
   • Put Value in Values (set to Sum if needed).
   • Add Region and Function as additional fields (for later filtering/slicers).
   • Add Date as a field (you can group it by Month, Quarter, Year in the PivotTable).
6. Build separate PivotTables for each comparison type Create multiple PivotTables from the same data source (copy the first PivotTable and adjust fields):

• Month-on-Month (MoM)
  • Rows: GL
  • Columns: Month (group Date by Months and Years).
  • Values: Sum of Value.
  • Add a calculated field or separate measure (if using Data Model/Power Pivot) to compute the difference between current month and previous month. This difference will be used for the bar chart.
• Quarter-on-Quarter (QoQ)
  • Rows: GL
  • Columns: Quarter (group Date by Quarters and Years).
  • Values: Sum of Value.
  • Use a similar calculation to show difference vs previous quarter.
• Year-on-Year (YoY)
  • Rows: GL
  • Columns: Year (group Date by Years).
  • Values: Sum of Value.
  • Add a calculation for difference vs same period previous year.
• Region-wise MoM/QoQ/YoY
  • For each of the above PivotTables, add Region to Filters (or Rows/Columns depending on layout).
  • Alternatively, create separate PivotTables where Region is in Columns and GL in Rows, still using Month/Quarter/Year groupings.
• Function-wise QoQ
  • Rows: GL
  • Columns: Quarter.
  • Filters or Columns: Function (or separate charts per Function).
  • Values: Sum of Value and difference vs previous quarter.

4. Create PivotCharts with GL on X-axis, Values on Y-axis, and Difference as bar For each PivotTable:
5. Click inside the PivotTable.
6. Go to PivotTable Analyze > PivotChart.
7. Choose a combo chart or clustered column chart:
   • Series 1: Sum of Value (e.g., line or column).
   • Series 2: Difference (MoM/QoQ/YoY) as a bar/column.
8. Use the PivotChart Analyze, Design, and Format tabs to:
   • Ensure GL is on the X-axis.
   • Values and Difference are on the Y-axis (Difference as bar graph).
   • Format colors and styles as needed.
9. Rename each PivotChart via PivotChart Analyze > PivotChart > Chart Name so they’re easy to identify.
10. Add Slicers for interactive filtering
11. Select any PivotTable.
12. Go to PivotTable Analyze > Filter > Insert Slicer.
13. Insert slicers for:
    • Date (or separate Year/Month fields if created).
    • Region.
    • Function.
14. Arrange and resize slicers on the dashboard sheet.
15. To connect slicers to all related PivotTables:
    • Select a slicer.
    • Go to Slicer > Slicer > Report Connections.
    • Check all PivotTables that should respond to that slicer (e.g., all MoM/QoQ/YoY and region/function views).
16. Add a Timeline for date-based flux selection
17. Select any PivotTable.
18. Go to PivotTable Analyze > Filter > Insert Timeline.
19. Choose the Date field.
20. Resize and place the Timeline on the dashboard.
21. Connect it to all PivotTables via Options (Timeline) > Report Connections.
22. Use the Timeline to select specific months/quarters/years; the PivotTables and charts will update, enabling:
    • MoM: select a single month; calculations compare to previous month.
    • QoQ: select a quarter; calculations compare to previous quarter.
    • YoY: select a month or year; calculations compare to same period prior year.
23. Arrange the dashboard

• Place all PivotCharts on a single worksheet.
• Group charts logically:
  • MoM (overall, region-wise, function-wise).
  • QoQ (overall, region-wise, function-wise).
  • YoY (overall, region-wise).
• Align slicers and Timeline for a clean layout.

8. Share the dashboard

• Save the workbook to SharePoint or OneDrive.
• Share with stakeholders; they can interact with slicers and Timeline to view GL vs Value and Difference across MoM/QoQ/YoY, Region, and Function.

---

References:

• Create and share a Dashboard with Excel and Microsoft Groups
• Create an Excel Services dashboard using SQL Server Analysis Services data
• Create an Excel Services dashboard using a Data Model (SharePoint Server 2013)