Power BI reports & dashboards for GST

Power BI reports & dashboards for GST

Indian finance teams in FY 2026-27 are drowning not in a shortage of GST data but in a surplus of it — scattered across GSTN portal downloads, ERP exports, e-invoice JSON files, and manually maintained Excel trackers. Power BI, configured correctly, collapses that scatter into a single governed workspace: live ITC exposure, filing status across every GSTIN, e-invoice failure rates, and refund pipeline, all visible in three clicks. This guide shows you exactly how to build it.

Why the Indian GST Stack Outgrows Excel

A business operating with five GSTINs across three states generates, every month, at minimum: five GSTR-1 returns, five GSTR-3B returns, five GSTR-2B auto-statements, an e-invoice IRN log, and an e-way bill register. That is over 60 files per quarter before you add purchase registers, vendor reconciliation workings, and notices from the department.

Excel can hold these files. It cannot govern them. When two analysts pull GSTR-2B data independently, they will produce different ITC figures because they used different download dates, different filter logic, or different GSTIN scopes. The CFO then receives two numbers in two decks and has no way to adjudicate.

Power BI solves the governance problem because the data model lives in one place. Every analyst, every branch, every entity reads from the same certified dataset. DAX measures encode the reconciliation logic once, and row-level security (RLS) ensures each user sees only the GSTINs they are authorised to view. The result is a defensible single version of the truth — which is exactly what a tax audit, a board presentation, or a GST officer enquiry demands.

Building the Data Model: the Foundation That Determines Everything

Every Power BI failure in GST reporting traces back to a weak data model. Spend twice as long here as you think you need to.

Calendar Table Aligned to the Indian Financial Year

DAX's built-in time intelligence functions assume a calendar year. GST does not. Build a custom DimDate table with these columns at minimum:

• Date (primary key)
• FY_Year — e.g., "FY 2026-27"
• GST_Period — "Apr-2026", "May-2026" … aligned to the GSTN portal's period naming
• FY_Month_Number — 1 for April, 12 for March, so PREVIOUSMONTH and DATESYTD work correctly
• Quarter_FY — Q1 FY27, Q2 FY27, etc.
• Is_Return_Due_Date — a boolean marking the 11th and 20th of each month for GSTR-1 and GSTR-3B due dates respectively

Without FY_Month_Number, your month-over-month and year-to-date measures will roll over in January instead of April, producing ITC trend lines that are meaningless against the GST return cycle.

GSTIN Dimension Table

Treat each GSTIN as a row in DimGSTIN with attributes:

• GSTIN (15-character alphanumeric key)
• State_Code (first two digits — "27" for Maharashtra, "07" for Delhi, etc.)
• Legal_Name and Trade_Name
• Registration_Type — Regular, Composition, ISD, NRTP
• Business_Vertical — maps to your cost-centre hierarchy
• Is_Active — flag inactive registrations to exclude from compliance KPIs

This table drives your row-level security. Map each Power BI user or security group to the GSTIN rows they may see. A branch controller in Pune should never be able to drill into Bengaluru's ITC data, even accidentally.

Fact Tables: Keep Them Granular

Build three core fact tables:

1. `FactGSTRReturn` — one row per GSTIN per period per return type (GSTR-1, 3B, 2B). Columns: filing status, date filed, tax liability (IGST/CGST/SGST/Cess), ITC claimed, cash paid.
2. `FactPurchaseInvoice` — one row per purchase invoice from your ERP or purchase register. Columns: vendor GSTIN, invoice date, taxable value, tax amount, HSN, ITC eligibility flag, 2B-match status.
3. `FactEInvoice` — one row per IRN. Columns: IRN, acknowledgement number, generation date, cancellation status, failure code if applicable.

Keep these fact tables at the lowest grain you can sustain. Pre-aggregating to monthly totals feels efficient until the GST officer asks for invoice-level detail during scrutiny — and then you have nothing to show.

The Five Dashboards Every Indian GST Function Needs

1. GST Liability Dashboard

Purpose: Show the net output tax liability by GSTIN, by HSN, and by month so the CFO can see quarterly cash outflow before GSTR-3B is filed.

Key measures:

• Total Output IGST / CGST / SGST / Cess — drawn from GSTR-1 data
• Liability_vs_PriorMonth % — period-over-period DAX using DATEADD
• HSN_Top10_by_Tax — a ranked table visual showing which HSN codes drive the largest liability

Add a conditional-formatting column that flags any HSN where the effective rate in the current month differs by more than 2 percentage points from the trailing three-month average. Rate mismatches are a common source of GSTR-3B errors and subsequent notices under Section 73 of the CGST Act, 2017.

2. ITC & GSTR-2B Reconciliation Dashboard

This is the highest-value dashboard in any GST Power BI workspace because it directly governs how much cash you actually pay.

Key measures:

• ITC_in_2B — total credit appearing in GSTR-2B for the period
• ITC_in_PurchaseRegister — ITC per your books
• ITC_Gap = ITC_in_PurchaseRegister − ITC_in_2B
• Vendors_Not_Filed_Count — vendors whose GSTR-1 is absent from 2B
• Rule37A_At_Risk_ITC — ITC from vendors who have not filed GSTR-3B; under Rule 37A of the CGST Rules, this ITC must be reversed if the vendor does not file within two years of the due date

Display ITC_Gap as a waterfall chart broken into three buckets: (a) vendor non-filing, (b) invoice mismatch (wrong GSTIN, wrong invoice number, wrong date), (c) timing difference (invoice uploaded in 2B after your cut-off). This decomposition tells the team exactly where to chase — vendor finance team, accounts payable team, or simply wait for next month's 2B.

3. Compliance Health Dashboard

Purpose: One-screen filing status across all GSTINs so no return slips past its due date unnoticed.

Design a matrix visual: rows = GSTINs, columns = return type × period (e.g., GSTR-1 Apr-2026, GSTR-3B Apr-2026). Cell values: "Filed", "Due", "Overdue", "NA". Use a traffic-light conditional format — green, amber, red.

Add a Late_Fee_Exposure card that calculates potential late fees in real time. Late fee for GSTR-3B is Rs. 50 per day (Rs. 25 CGST + Rs. 25 SGST) for returns with tax liability, capped at Rs. 10,000 per return. For nil returns the rate is Rs. 20 per day. Interest on delayed tax payment runs at 18% per annum under Section 50 of the CGST Act.

Also surface open notices from the department: GST DRC-01, DRC-01A, DRC-03 and show their response deadlines. Missed notice responses are a far bigger financial exposure than late filing fees.

4. E-Invoice and E-Way Bill Dashboard

The e-invoice mandate currently applies to taxpayers with aggregate annual turnover exceeding Rs. 5 crore (as notified). For these businesses, every B2B supply must carry a valid IRN generated on the Invoice Registration Portal (IRP) before the document is issued. An invoice without an IRN is not a valid tax document and the recipient cannot claim ITC.

Key metrics:

• IRN_Success_Rate % — IRNs generated / invoices attempted
• IRN_Cancellation_Rate % — cancellations within 24 hours (the only window permitted)
• Top_Failure_Codes — a bar chart of IRP error codes; code 2150 (duplicate invoice number) and 2283 (GSTIN mismatch) are the most common in practice
• E-Way_Bill_Expiry_Today — a table of active e-way bills expiring within 24 hours, refreshed via scheduled dataflow

Connect to your ASP or GSP's API to pull IRN data daily. If you are downloading manually from the IRP portal, automate the extract using Power Automate → SharePoint → Power BI dataflow. Avoid direct manual uploads: they introduce human error and break the refresh schedule.

5. Refund Tracking Dashboard

GST refunds — particularly on account of inverted duty structure under Section 54 of the CGST Act or export refunds — can represent significant working capital. A company exporting software services with zero-rated supplies may have Rs. 30–50 lakhs locked in pending refund applications at any given time.

Track: application date, ARN, amount claimed, deficiency memo (RFD-03) issuance, provisionally sanctioned amount (RFD-04), final sanction (RFD-06), and days elapsed at each stage. A Days_Pending measure against a 60-day statutory processing target under Rule 91 gives the finance head a clear escalation trigger.

Worked Example: GSTR-2B Reconciliation in Power BI

ABC Components Pvt Ltd, Mumbai (GSTIN: 27ABCDE1234F1Z5) — auto-ancillary manufacturer.

In May 2026, the purchase team processed 612 purchase invoices with a total taxable value of Rs. 1.44 crore. The expected ITC based on purchase register data:

GSTR-2B downloaded on 14 June 2026 shows:

Gap: Rs. 2,12,000. The Power BI reconciliation dashboard breaks this down automatically:

• Vendor non-filing (18 vendors): Rs. 1,38,000 — these vendors have not uploaded their GSTR-1. ABC must chase payment from these vendors or withhold future purchase orders. Under Rule 37A, if these vendors do not file within 2 years, Rs. 1,38,000 must be reversed with 18% interest.
• Invoice number mismatch (7 invoices): Rs. 52,000 — the vendor typed "INV/26-27/1045" in GSTR-1 while the purchase order says "1045/2026-27". The ITC is legitimately available but will not auto-match. Raise a vendor amendment request.
• Timing difference (3 invoices): Rs. 22,000 — invoices uploaded in GSTR-1 after the 2B generation date. These will appear in June 2026's 2B automatically.

Without Power BI, this reconciliation takes a senior GST executive two days in Excel. With the dashboard, the gap figure, its composition, and the vendor-level drill-through are visible in under two minutes — every month, without manual effort.

Step-by-Step: Connecting Your Data Sources

1. GSTR-2B JSON/Excel: Download from gst.gov.in → Returns → GSTR-2B. Store in a dedicated SharePoint folder. Connect Power BI via SharePoint Online connector → combine files from folder → Power Query transformation to normalise multi-sheet Excel structure.
2. ERP purchase data: If your ERP is SAP or Oracle, use the SQL Server connector (via on-premises data gateway) to query the purchase ledger directly. For Tally, export to Excel on a scheduled basis via Tally's ODBC connector, then land in SharePoint.
3. GSTR-1 filed data: Connect via your GSP's API if available, or download GSTR-1 summary JSON and parse in Power Query. The JSON structure is documented on the GSTN sandbox portal.
4. E-invoice IRN log: Pull from your ASP's dashboard as CSV. Automate with Power Automate if your ASP supports webhook or API export.
5. Gateway setup: Install the on-premises data gateway on a server inside your network. Add it to your Power BI tenant under Admin portal → Gateways. Schedule refresh at 6 AM daily so dashboards are current by the time the GST team starts work.

Never connect directly to gst.gov.in from Power BI. The portal does not offer a machine-readable API for registered taxpayers (only for GSPs under the GST Suvidha Provider framework). Your data pipeline must go through a controlled extract-load stage, not live web queries.

Designing Visuals That Drive Decisions, Not Just Reports

A common mistake: building a Power BI report that looks like an Excel pivot table. That is not a dashboard; it is a replica of the problem you were trying to solve.

Design every page starting from the decision it must enable:

• "Do we have a cash shortfall in ITC this month?" → A single KPI card showing ITC gap in rupees, with a drill-through button to vendor-level detail. The CFO needs this in five seconds, not after navigating three pages.
• "Which GSTIN is at risk of a late filing penalty?" → A matrix with traffic-light formatting. Red cells are actionable; the user clicks through to see the specific return and its due date.
• "Is our e-invoice process working?" → A trend line of IRN success rate over 12 months, with failure-code annotations on the months where it dipped.

Use bookmarks to create a "CFO view" (summary KPIs only) and a "GST Manager view" (full drill-down) within the same report, without duplicating the data model. Tooltips can surface invoice-level detail on hover without cluttering the main visual. Keep colour usage disciplined: red for non-compliance or deficit, amber for approaching threshold, green for within target — and use these consistently across every report in the workspace.

Security, Gateway Architecture and DPDP Compliance

Row-level security is non-negotiable when GST data from multiple entities sits in one workspace. Configure RLS in Power BI Desktop:

1. Create a role (e.g., GSTIN_Access) with a DAX filter on DimGSTIN[User_Email] = USERPRINCIPALNAME().
2. Map each user's corporate email to the GSTIN rows they may view.
3. Test the role from Power BI Desktop → Modeling → View as Role before publishing.

Under the Digital Personal Data Protection Act, 2023 (DPDP Act), any dataset containing personal data — vendor PAN, supplier contact details, customer shipping addresses pulled into e-invoice records — requires a documented processing purpose and appropriate access controls. In practice: strip PAN and personal contact fields from the fact tables at the Power Query stage unless there is a specific analytical need. Keep GSTIN (which is a business identifier, not a personal identifier for companies) but mask individual proprietor GSTINs where the proprietor's identity is visible. Disable Excel export on reports containing vendor master data — Power BI's export restriction is in Report Settings → Export data → "None".

Common Mistakes and Pitfalls to Avoid

• Refreshing 2B data before the 14th: GSTR-2B is generated by GSTN on the 14th of the month following the tax period. A refresh run on the 12th will pull the prior month's 2B again. Build a date guard in Power Query: if Date.Day(DateTime.LocalNow()) < 14 then [prior_month_2B] else [current_month_2B].
• Comparing GSTR-2B with GSTR-2A: GSTR-2B is the static, locked statement that governs ITC eligibility under Rule 36(4). GSTR-2A is dynamic and has no regulatory standing for ITC claims since 2021. Build your reconciliation on 2B only.
• Ignoring ineligible ITC in the model: Section 17(5) of the CGST Act blocks ITC on a defined list of items — motor vehicles (with exceptions), food and beverages, club memberships, construction services for immovable property (for own use), and others. Flag these invoices in FactPurchaseInvoice[ITC_Eligibility] at the data layer. If you leave this to the analyst to filter manually each month, the blocked ITC will eventually find its way into the 3B claim and trigger a notice.
• Building measures without a measure dictionary: Six months after go-live, no one remembers whether ITC_Net includes Rule 37A reversals or not. Document every measure — name, formula, description, last reviewed date — in a SharePoint list linked from the Power BI workspace description.
• Setting refresh to hourly when GST data changes daily at best: Unnecessary refreshes consume gateway capacity and Power BI Premium capacity units. A once-daily scheduled refresh at 6 AM, with a manual on-demand refresh after GSTR-2B generation on the 14th, is all most GST dashboards need.

Retiring the Shadow Spreadsheet Stack

Every Power BI GST rollout inherits a shadow stack: the GSTR-3B working in Sheet 1, the ITC reconciliation in Sheet 2, the CFO deck in a separate workbook that someone emails at 9 PM on the 19th of each month. These spreadsheets do not disappear because Power BI exists; they disappear only when the people who made them trust the Power BI numbers more than their own workbooks.

The transition requires a deliberate decommissioning process: identify each material spreadsheet, replicate its core output as a certified report or certified measure in Power BI, run both in parallel for one full GST cycle, reconcile the outputs, and get sign-off from the spreadsheet owner before marking the file as archived. This is unglamorous work that takes three to four months for a mid-sized finance function. It is also the difference between a strategic analytics layer and an expensive viewer that nobody actually relies on.

Key Takeaways

• Build your data model first: a calendar table aligned to the Indian financial year and a GSTIN dimension with RLS are prerequisites, not optional extras.
• GSTR-2B — not GSTR-2A — is the correct basis for ITC reconciliation in Power BI after the 14th of each month.
• The five essential dashboards are: Liability, ITC Reconciliation, Compliance Health, E-Invoice/E-Way Bill, and Refund Tracking. Start with whichever causes the most manual work today.
• A worked ITC gap (such as the Rs. 2,12,000 shortfall in the example above) must be decomposed into vendor non-filing, invoice mismatches, and timing differences so each category gets the right remedial action.
• Row-level security mapped to GSTIN is non-negotiable; combine it with DPDP-compliant data masking for any dataset holding personal identifiers.
• Disable Excel export on sensitive ITC reports; document all measures in a measure dictionary accessible to auditors.
• Decommission shadow spreadsheets deliberately, one at a time, with parallel-run sign-off — otherwise Power BI and Excel will coexist indefinitely, producing two versions of the truth.