Procurement analytics from SharePoint, Excel, JSON and on-prem SQL: your Fabric approach?

Hi kiara-mehra35​,

The ingestion and layering points are well covered above, so I'll take your currency question, because it's the one that tends to cause rework later. My rule of thumb: convert to one reporting currency in the pipeline, keep the local amount too, and only use DAX for "view in another currency" on demand.

Silver: FX rates table (Date, FromCcy, ToCcy, Rate) from your treasury/ERP source
Gold fact (PO lines / supplier spend):
  AmountLocal, LocalCcy, AmountUSD (converted at transaction date), FxRateUsed
-> savings and raw-material cost trends compare in USD without DAX at query time
-> FxRateUsed makes every number auditable

from pyspark.sql import functions as F

fx  = spark.read.table("silver.fx_daily").filter("to_ccy = 'USD'")
po  = spark.read.table("silver.po_lines")

gold = (po.join(fx, (po.order_date == fx.rate_date) & (po.currency == fx.from_ccy), "left")
          .withColumn("fx_rate_used", F.when(po.currency == "USD", F.lit(1.0)).otherwise(fx.rate))
          .withColumn("amount_usd", F.col("amount_local") * F.col("fx_rate_used"))
          .drop("rate", "rate_date", "from_ccy", "to_ccy"))

gold.write.mode("overwrite").saveAsTable("gold.fact_po_lines")

Then a light DAX layer only for the cases where users want a different currency (Direct Lake friendly, since it's a simple multiply at the reporting grain):

Spend (Selected Ccy) =
VAR ccy = SELECTEDVALUE ( DimCurrency[Code], "USD" )
RETURN
    IF ( ccy = "USD", SUM ( FactPO[amount_usd] ),
         SUMX ( VALUES ( DimDate[Month] ),
                CALCULATE ( SUM ( FactPO[amount_usd] ) ) * [Avg USD->Ccy Rate] ) )

Two decisions to settle with finance up front, because they change the numbers: transaction-date vs month-average rates for savings, and whether "raw material cost change" should be shown at constant currency (same FX rate for both periods) so FX swings don't look like price changes.

So my overall approach would be:

FX table in Silver -> convert to reporting currency in Gold (+ keep local + rate used)
-> DAX only for optional display currency
-> agree FX policy (transaction vs average, constant-currency view) with finance

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.