Power BI Fundamentals and Microsoft Ecosystem Questions

Rolling12MoAvg_Sales :=
VAR CurrentDate = MAX(dim_date[Date])
VAR StartDate = EDATE(CurrentDate, -11)  -- include current month + previous 11 months
VAR DateRange =
    FILTER(
        ALL(dim_date),
        dim_date[Date] >= StartDate
        && dim_date[Date] <= CurrentDate
    )
VAR TotalSales =
    CALCULATE(
        SUM(FactSales[SalesAmount]),
        DateRange,
        REMOVEFILTERS(dim_date)  -- ensure full range is considered even if model filters applied elsewhere
    )
VAR MonthsWithData =
    DISTINCTCOUNT(
        FILTER(
            ADDCOLUMNS(
                VALUES(dim_date[Date]),
                "@Sales", CALCULATE(SUM(FactSales[SalesAmount]))
            ),
            [@Sales] <> BLANK()
        )
    )
-- If you want true average across 12 months regardless of missing data, use 12. 
-- Here we average over months present in the 12-month window (ignores completely missing months).
RETURN
IF(
    MonthsWithData = 0,
    BLANK(),
    TotalSales / MonthsWithData
)

-- example for Azure SQL DB
CREATE TABLE dbo.Stage ( ... ) ON [PRIMARY] (PARTITION (DateRangeScheme));
-- load data into Stage
ALTER TABLE dbo.Stage SWITCH PARTITION 202512 TO dbo.Fact PARTITION 202512;

-- example CTAS approach (Synapse)
CREATE TABLE dbo.Fact_New WITH (DISTRIBUTION = HASH(UserId), CLUSTERED COLUMNSTORE INDEX) AS
SELECT * FROM dbo.Fact WHERE 1=0; -- create empty structure
INSERT INTO dbo.Fact_New PARTITION (PartitionExpr) SELECT ... FROM dbo.Stage;
-- then use metadata swap (DROP/RENAME)

TotalRevenue = SUM(fact_sales[revenue])
RevenueYTD = CALCULATE([TotalRevenue], DATESYTD(dim_date[date]))